IFS vs nested IF

IFS vs. nested IF

Excel IF Statement

Syntax of Excel IF Statement:​

= IF ( Logical Test , Value if True , Value if False )​​

Logical test Examples:

• Equal to: A1=A2.
• Higher than: A1>A2.
• Higher or equal than: A1>=A2.
• Less than: A1<A2.
• Less or equal than: A1<=A2.
• Not equal to: A1<>A2.

If we do not declare a third argument, because it is not mandatory, it will return FALSE by default. If we want to return the text as a result in the second or third argument, it must be enclosed in quotation marks.

=IF(A1>5,"Pass","Fail")

If cell A1 value is higher then five, formula return Pass otherwise Fail.

In the case when we want to apply one logical condition, creating an IF function is simple and obvious. However, if we want to introduce further conditions, everything gets slightly more complicating. In this case, the function is nested, the next function (function in function or multiple) as an argument of the function.

Nesting of the IF function

I think each of us, at least once, has found it difficult to master the IF function when it is nested. That is, when the IF function was an argument of the IF function at least once. In addition, the nested function is a burden on the work of the system during conversion, of course, when we often use this function in the worksheet.
I will now give you a simple example of an IF function nested once. In place of the third argument, a new IF function containing another condition is introduced.

= IF (( Logical Test , Value if True , IF ( Next Logical Test , Value if True , Value if False ))
=IF(A1>5,"Excellent",IF(A1>3,"Good","Poor"))

If cell A1 value is higher then five, formula return Excellent, if not but higher than three return Good,  otherwise Poor.

IFS function alternative

In 2016, an alternative to the IF function appeared in Excel, i.e. the IFS function, which gives the possibility to introduce more logical conditions than one, which simplifies the creation of the formula and significantly speeds up its work.

= IFS ( First logical Test , Value if True , [ Next Logical Test , Value if True ] , ...)
=IFS(A1>5,"Excellent",A1>3,"Good")

If cell A1 value is higher then five, formula return Excellent, if not but higher than three return three,  otherwise return error #NA.

It can happen that more than once the condition is met in the arguments of the IFS function. In this case, the one that is recorded first is decisive.

=IFS(A1>3,"Good",A1>5,"Excellent")

In this case, if the content of cell A1 is higher than three, the function will always return Good (never return Excellent). Therefore, the order in which the conditions are saved is important. If none of the conditions are met, the formula will return error #NA. 

IFS vs nested IF

Benefits of IFS over nested IF:

• The IFS function allows you to easily enter further logical conditions without nesting.
• Due to the lack of nesting, the performance of IFS is much more efficient
• Allows you to apply twice as many, or 127 conditions, while the nested formula 64 conditions only.
• If none of the conditions are met, IFS will return error #NA. In this case, it is worth using IFEROOR. In this case, in the second argument we give what should be if an error occurs, if we need it.

= IFERROR ( IFS ( Logical Test , Value if True , Next Logical Test , Value if True ) , Value if False )
=IFERROR(IFS(A1>5,"Excellent",A1>3,"Good"),"Poor")

If the value in cell A1 is higher than five, the formula return Excellent, if not, but higher than three return Good, otherwise it return Poor.

You can learn similar tips and tricks in Excel at a comprehensive Training in London.

Leave a Reply

Your email address will not be published. Required fields are marked *