# Use the new Excel IFS function instead of nested IF

From this short tutorial you'll learn about the new IFS function and see how it simplifies writing nested IF in Excel. You'll also find its syntax and a couple of use cases with examples.

Nested IF in Excel is commonly used when you want to evaluate situations that have more than two possible outcomes. A command created by nested IF would resemble "IF(IF(IF()))". However this old method can be challenging and time consuming at times.

The Excel team has recently introduced the IFS function that is likely to become your new favorite one. Excel IFS function is available only in Excel 2016 that is part of Office 365 subscriptions, Excel Online and Mobile, Excel for Android tablets and phones.

### The Excel IFS function - description and syntax

The IFS function in Excel shows whether one or more conditions are observed and returns a value that meets the first TRUE condition. IFS is an alternative of Excel multiple IF statements and it is much easier to read in case of several conditions.

Here's how the function looks like:

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2]…)

It has 2 required and 2 optional arguments.

• logical_test1 is the required argument. It's the condition that evaluates to TRUE or FALSE.
• value_if_true1 is the second required argument that shows the result to be returned if logical_test1 evaluates to TRUE. It can be empty, if necessary.
• logical_test2…logical_test127 is an optional condition that evaluates to TRUE or FALSE.
• value_if_true2…value_if_true127 is an optional argument for the result to be returned if logical_testN evaluates to TRUE. Each value_if_trueN relates to a condition logical_testN. It can also be empty.

Excel IFS lets you evaluate up to 127 different conditions. If a logical_test argument doesn't have certain value_if_true, the function displays the message "You've entered too few arguments for this function". If a logical_test argument is evaluated and corresponds to a value other than TRUE or FALSE, IFS in Excel returns the #VALUE! error. With no TRUE conditions found, it shows #N/A.

### The IFS function vs. nested IF in Excel with use cases

The benefit of using the new Excel IFS is that you can enter a series of conditions in a single function. Each condition is followed by the result that will be used if the condition is true making it straightforward to write and read the formula.

Let's say you want to get the discount according to the number of licenses the user already has. Using the IFS function, it will be something like this:

`=IFS(B2>50, 40, B2>40, 35, B2>30, 30, B2>20, 20, B2>10, 15, B2>5, 5, TRUE, 0)` Here's how it looks with nested IF in Excel:

`=IF(B2>50, 40, IF(B2>40, 35, IF(B2>30, 30, IF(B2>20, 20, IF(B2>10, 15, IF(B2>5, 5, 0))))))` The IFS function below is easier to write and update than its Excel multiple IF equivalent.

`=IFS(A2>=1024 * 1024 * 1024, TEXT(A2/(1024 * 1024 * 1024), "0.0") & " GB", A2>=1024 * 1024, TEXT(A2/(1024 * 1024), "0.0") & " Mb", A2>=1024, TEXT(A2/1024, "0.0") & " Kb", TRUE, TEXT(A2, "0") & " bytes")` `=IF(A2>=1024 * 1024 * 1024, TEXT(A2/(1024 * 1024 * 1024), "0.0") & " GB", IF(A2>=1024 * 1024, TEXT(A2/(1024 * 1024), "0.0") & " Mb", IF(A2>=1024, TEXT(A2/1024, "0.0") & " Kb", TEXT(A2, "0") & " bytes")))` If you need more examples of using nested Ifs, have a look at

### 14 Responses to "Use the new Excel IFS function instead of nested IF"

1. Mikey says:

The hyperlink above for the SWITCH tutorial is broken (it has the sitename twice). Correct URL for the interim is https://www.ablebits.com/office-addins-blog/2016/05/02/excel-switch-function/

• Mikey says:

self-reply: same issue affects all three URL in the "You may also be interested" section.

• Maria Azbel (Ablebits.com Team) says:

Thank you so much for pointing this out to us, Mike! Fixed :)

2. Faye says:

I need excel function to calculate and update old selling price of same product number located in different rows in same column
example:

Product#1001-099
Old Price = \$20.00
New Price should be increased by 5%

Product is in Column A
Selling Price is Column D

How do I increase and replace the old Selling Price
for this product that is in Column A,
Row 8, Row 101, Row228?

3. Renat Tlebaldziyeu (Ablebits.com Team) says:

Hi Faye,

You need to add the additional column in your table and use the following formula to increase the price by 5%:
=D1*1.05
Then please copy the added column and use the Paste Special - Values option to replace the values in Column D.

4. Fred says:

5. Mahmood says:

hi
why in my excel(2010 version) "IFS" function does not work??
what should I do??

• Charlie says:

6. Mike De Butts says:

with nested IF(), latter redundant calculations are ignored (not calculated)

=IF(1=1,"Answer","this bit could be processor heavy but is ignored")

but using the 'evaluate formula' it would appear that IFS() works out all the bits of the formula, even if the first condition is met and therefore is redundant

=IFS(1=1,"answer", other condition IS verified, redundant terms all calculated)

Is this the case?

essentially, IFS does indeed look simpler to untangle, BUT is it actually getting excel to do a less efficient amount of calculating?

• Gennady Terekhov (Ablebits.com Team) says:

Hello, Mike,

We haven’t investigated the efficiency of the IF() and IFS() functions in all configurations.
Most likely, they work in the same way. For example, if you create two formulas like the ones below:
=IF(1=1,"1",IF("=1","2"))
=IFS(1=1,"1", "=1", "2")
They return the same result equal to «1».
But following your hypothesis, the =IFS(1=1,"1", "=1", "2") formula should have returned a #VALUE! error as the second condition in the formulas is wrong.

• Brady says:

Mike you are correct. It appears as though IFS is horribly inefficient compared to traditional nested IFS statements. Much easier to write but you end up sitting around longer waiting on calculations to finish.

7. Abdu says:

Hi everybody...
I have problem related, I think, to using IF function or IFS.
I have a Pivot filter including 3 options, means I can select 1 or All or Multiple product categories. I am using SUMIFS to extract the data from Excel table, and filter using Pivot filter. all working fine until I select 2 product categories, I eliminate the problem when selecting (ALL), but could not do that when I select 2 product categories, the result was all zero values. here is my function:

=SUMIFS(SalesData[revenue];SalesData[year];I\$4;SalesData[region];\$H5;SalesData[Category];IF(\$I\$2="(All)";"*";\$I\$2))

my attempt was nested IF:
IF(\$I\$2="(All)";"*";IF(\$I\$2="(Multiple Items)";\$I\$2))
but it dose not work, any help...

8. Cat M Bourque says:

Every 2 weeks a payment is made and entered, the beginning amount of payment are decreased by 1 every second Thursday. During the off weeks an extra payment can be made and is entered in a separate Col. Col 3. When the extra payment is made i would like the reflection of that payment shown in Col 2 by subtracting from the number of payments remaining and not effecting the bi-weekly payment / remaining payments.

Payments remaining = 110

Col. 1 has a payment of \$200.00
Col. 2 has 109 (number of payments remaining)
with an IF statement,

-if(col. 1>0,payments remaining-1,"")

Note that each payment made the IF statement changes from Payments remaining-1 to Col. 2 (previous cell number) -1 so it would be

-if(col. 1>0,Previous cell-1,"") Previous cell being 109

In addition to this there is another Col, Col. 3 for EXTRA payments where, when entered on a row with no other payments entered, i would like THAT payment reflected in the payments remaining total.

If you want the spreadsheet let me know .... can email it to you

Thanks
Cat

9. Dharmendra Rana says:

Salab Calls form-To Incentive per calls
Slab1 1841-2084 2.50₹
Slab2 2025-2116 3.50₹
Slab3 2117-2207 4.50₹
Slab4 2208-2300 5.50₹
Slab5 2301-Above 10₹ how to calculate in total rupees by Excel formula.

60+ professional tools for Excel