
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:
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:
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.
How do your Excel skills stack up?
Test NowNext up:
- Frame fun in InDesign
- How to be positive and enthusiastic at work after a long weekend!
- Nintex: Submit a form to a different page
- An introduction to PRINCE2® Agile
- Why we all need Emotional Intelligence
- “How was the training?”…“Yeah good thanks, now what’s for lunch?”
- Exchange Server 2016 features and updates – Part 1
- Beguile for a while with a smile
- Exchange Server 2016 features and updates – Part 2
- Make multiple copies of a Microsoft Excel spreadsheet in seconds
Previously
- Frame fun in InDesign
- Put your Photoshop skills into practice
- The importance of acknowledgement
- Semantic Tags and the File API in HTML5
- Using PowerShell to upload files to your Azure Storage Container
- Lightroom vs. Photoshop’s Camera Raw
- The importance of a break-even point in budgeting
- How-to: Put SharePoint calendars into Outlook
- Unleash creativity in the workplace
- How to get the most out of Outlook