IFS Function

Feb 18, 2021 | Excel Hints and Tips

The IFS function enables you to perform multiple outcomes based on multiple conditions. This significantly simplifies how to perform this sort of task.

Until recently you would have needed to combine IF functions inside of IF functions which was referred to as nested Ifs which led to a complicated calculation with a significant chance of mistakes being made.

Fortunately, from Excel 2016 onward a new function called IFS was introduced.  This facilitated multiple outcomes using just 1 function which is so much easier. 

Structure

=IFS(logical test 1, value if true 1, logical test 2, value if true 2, …………..) 

There can be up to 127 groups of information using the IFS function. 

Arguments

Logical test 1 – Any value or expression that can be evaluated to true or false 

Value if true 1 – Value returned if the logical test is true 

This is then repeated with a new expression for logical test2 and value if true 2 and so on. 

Example

The following example looks at changing an exam mark into an exam grade.  If the exam mark is greater than or equal to 90 they receive a Distinction, from 80 up to but not including 90 they receive a Merit, from 60 up to but not including 80 they receive a Pass and less than 60 they Fail. 

=IFS(B2>=90,”Distinction“,B2>=80,”Merit”,B2>=60,”Pass”,B2<60,”Fail”) 

Image to show the IFS function in use on Excel

NOTE: The function does not need to include an upper limit for any of the ranges because once the calculation hits a result that is true it doesn’t look any further in the function.  This will work as long as the figures start at the top and work down or the bottom and work up.  If the order is totally random then the function will become hugely more complicated. 

The IFS function is just one of many functions that have been added over the last few versions of Excel to simplify more complicated calculations.  XLOOKUP and IFERROR are just 2 of these. 

Further Reading

If you’ve enjoyed reading about how to use the IFS function on Excel, there’s some other blogs below that you might find useful:

Want to learn more about other Excel features? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Courses I run.

MEMBERSHIPS

FOLLOW LARA