Decisions made easily with Excel

 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.

=IF(condition,TRUE, FALSE) could become =IF(B2>40,”Pass”,”Fail”)

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:


Value Result
>=80 A
>=60 B
>=40 C
~ Fail

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

=IF(B2>=80,”A”,IF(B2>=60,”B”,IF(B2>=40,”C”,”FAIL”)))

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.

=IFS([Something is True1, Value if True1, [Something is True2, Value if True2],…[Something is True127, Value if True127])

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

=IFS(B2>=80,"A",B2>=60,"B",B2>=40,"C")

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:

=IFS(B2>=80,"A",B2>=60,"B",B2>=40,"C",TRUE,"Fail")

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.


How do your Excel skills stack up?   

Test Now  

How do your Excel skills stack up?   

Test Now  

About the Author:

Steve Wiggins  

Steve is a highly experienced technical trainer with over 10 years of specialisation in Software Application Development, Project Management, VBA Solutions and Desktop Applications training. His practical experience in .NET programming, advanced solution development and project management enables him to train clients at all levels of seniority and experience. Steve also currently manages the IT infrastructure for New Horizons of Brisbane, providing him with daily hands-on experience with SCCM, Windows Server 2012 and Windows 8.

Read full bio
top
Back to top