What is the return policy?

Local and State sales tax will be collected if your recipient's mailing address is in

The Excel IFS function excels in data handling by accommodating multiple conditions effortlessly. It combines the simplicity of the IF function while enabling you to apply it across multiple conditions simultaneously. In this article, you will discover how to utilize this powerful feature and understand its significance in handling complex data scenarios.

What is the IFS Function in Excel?

Let’s first understand what Excel IFS is. The Excel IFS is a powerful logical function that allows you to test multiple conditions and return a value based on the first true condition. It is an alternative to using nested IF statements, making complex logical evaluations much simpler and easier to manage.

The syntax of the IFS function is as follows:

=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2)

The function evaluates Excel IFS multiple conditions one by one, and when it finds the first true condition, it returns the corresponding value_if_true. If none of the logical tests are true, it returns the value_if_false.

How to Use the IFS Function in Excel?

Using the Excel IFS function is easy and simple. First, select the cell where you want the result to show up. Then, type the IFS formula in that cell, following the pattern we mentioned earlier. You need to replace "value_if_true1," "value_if_true2," and so on, with the values you want to appear if the condition is true for each respective test. Once you have entered the formula, just press the Enter key, and the result will be calculated and displayed in the cell you selected.

To understand how to add the IFS function in Excel in a better way, take a look at three different scenarios below where we applied the Excel IFS function:

Example 1

Let's say we have a table containing students' scores and we want to assign grades based on the score using the IFS function.

The school’s grade system works as follows:

- A: Marks equal to or over 90

- B: Marks equal to or over 80 and less than 90

- C: Marks equal to or over 70 and less than 80

- D: Marks equal to or over 60 and less than 70

- F: Marks less than 60

so, the formula will be:

=IFS(B2 >= 90, "A", B2 >= 80, "B", B2 >= 70, "C", B2 >= 60, "D", B2

To apply this grading system, follow the steps below to apply the Excel IFS function:

Step 1: In cell C2, we will start the IFS function with an Equals sign.

Step 2: Next, we will be entering the conditions accordingly. Make sure each condition is separated by a comma and each Grade is written inside inverted commas.

Step 3: Hit Enter and the grade corresponding to the score in cell B2 will appear in cell C2 based on the grade system defined in the formula.

Step 4: To apply the formula to other cells, you can copy and paste the formula to the desired cells. Alternatively, you can click and drag the small square at the bottom right corner of cell C2 (the fill handle) to auto-fill the formula into adjacent cells.

Now, the grades will be calculated automatically for each student's score, and you can easily extend the formula to cover the entire table of scores and grades by dragging it to other cells.

Example 2

Suppose we have a sales data table, and we want to categorize sales performance based on the sales amount as follows:

- Excellent: Sales amount equal to or over 3000.

- Good: Sales amount equal to or over 2000 and less than 3000.

- Satisfactory: Sales amount equal to or over 1000 and less than 2000.

- Need Improvement: Sales amount less than 1000.

So, the formula will be

=IFS(B2 >= 3000, "Excellent", B2 >= 2000, "Good", B2 >= 1000, "Satisfactory", B2

Step To apply these Performance remarks, simply enter the IFS function in cell C2 followed by its conditions.

Example 3

Let's consider an inventory management scenario where we want to categorize products based on their stock levels as follows:

- High: Sales amount equal to or over 200.

- Moderate: Sales amount equal to or over 100 and less than 200.

- Low: Sales amount less than 100.

so, the formula will be

=IFS(B2 >= 200, "High", B2 >= 100, "Moderate", B2

Step To apply the stock category, we will enter the IFS function in the C2 followed by stock categories as conditions, and subsequently, drag down the results to the desired cells.

Tips

When using the Excel IFS function, always start with the most specific conditions and gradually move towards the more general ones.
You can use up to 127 pairs of logical_test and value_if_true in the IFS function.
If you need to check for additional conditions, you can consider using nested IFS functions.
Make sure to handle all possible scenarios by including a value_if_false at the end of the IFS function.
The text values in the IFS function will not be recognized by Excel if inverted commas are not used, and this results in #ERROR! Make sure inverted commas are, i.e. “B”, or “PASS”

Trustpilot

stars

WPS Office- Free All-in-One Office Suite

Use Word, Excel, and PPT for FREE, No Ads.
Edit PDF files with the powerful PDF toolkit.
Microsoft-like interface. Easy to learn. 100% Compatibility.
Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User

avator

Algirdas Jasaitis

logo

Free Editing Excel, Word, PPT - WPS Office

WPS Office is a modern and powerful collection of office tools that offers a free and complete alternative to popular software like Excel, Word, and PowerPoint. It's designed to be easy to use for both individuals and businesses. The best part is that you don't need to be an expert in Excel to use it.

WPS Office has a simple interface with handy functions, so you can get started quickly. The suite provides various features and tools to help you with different office tasks like creating, editing, and sharing documents effortlessly. Whether you need to make spreadsheets, write professional documents, or create captivating presentations, WPS Office has everything you need. Also, did we mention that all of these features are free? Yes, a free alternative to Microsoft Excel to cater all your data management needs.

FAQs

Q1. Why is the IFS function not giving results?

If the IFS function is not giving results, it might be because your Excel version doesn't support it. Make sure you have a newer version of Excel (2016 or later) that includes the IFS function.

Q2. How can I combine IFS and formulas in Excel?

You can combine the IFS function with the AND function in Excel. By using the AND function within the logical_test of the IFS function, you can evaluate multiple conditions together and return results based on those combined conditions.

Q3. What can I use instead of the IFS function?

If your Excel version doesn't have the IFS function, you can use nested IF functions as an alternative. They work similarly to the IFS function and allow you to check multiple conditions to get the desired result.

Master IFS With WPS Spreadsheet

In this article, we have delved into multiple examples to gain a deeper understanding of the Excel IFS function. Not only will you learn how to use this function effectively, but this guide will also demonstrate various scenarios where you can apply it. One key takeaway is that WPS Spreadsheet might surpass Excel, considering its similarity in features and the added benefit of being free. Don't miss out; download WPS Spreadsheet today!