Sep 30, 2016

So, we’re in Excel and we have to apply some conditional logic. Hopefully, you are familiar with the IF function. Basically we provide a condition that will evaluate to TRUE or FALSE and then supply what we would like to see in the cell for each part.

This would check the contents of B2 and provided the value found was greater than or equal to 40, it would put the string ‘Pass’ in the cell, otherwise, it would put the string ‘Fail’.

What if our condition was a little more complex? Let’s say we had the following rules:

We could still use the IF function, but we would need to nest them inside of one another, so it would become the following:

This is all well and good, however, we could reach the situation where we have too many conditions to evaluate and, at that point, Excel tells us there is an error in the formula.

Well, salvation is at hand. Provided you are using Excel 2016 and have the June 2016 updates you have automagically received the IFS function. Along the same lines as the COUNTIFS and SUMIFS function, it allows for many conditions and much simpler syntax.

So, taking our earlier example, the formula would become:

This simply says, if B2 is greater than or equal to 80, enter an ‘A’ in this cell. If B2 is greater than or equal to 60, enter a ‘B’ into this cell and so on.

One thing IFS doesn’t appear to allow is your ‘default’ condition, in other words, if all other conditions return a false what do we put in the cell. Well, we can still do this using IFS, we just need to enter a condition that will always evaluate to TRUE.

What better way of doing this than to use TRUE itself.

So the formula would become:

As you can see from the below image, it works like a charm

Our formula now checks the first 3 conditions, none of them evaluate to TRUE. When it gets to the last condition, TRUE is most assuredly TRUE and therefore it puts ‘Fail’ in the cell. It can seem a bit backwards to begin with, however, you soon get used it.

IFS is a great addition to Excel and makes your decision structures much easier and manageable.

For more information, check out our Excel Training courses.