Excel 2016: IFS Function

The following post was created by Kasper Langmann as a guest post for Excel Exposure.  If you’d like to see more of his work, check out Spreadsheeto for more Excel tutorials.

Follow along with this workbook that he created specifically for Excel Exposure!

How To Use the IFS Function

With the release of Excel 2016, Microsoft included several new functions. One of those new functions is the ‘IFS’ function. The ‘IFS’ function builds upon the ‘IF’ function in such a way that it simplifies one of the most common uses of the ‘IF’ function.

In order to have a discussion about the ‘IFS’ function, it would be a good idea to review the basics of the ‘IF’ function. Both functions are logical functions which means that they return a value based on whether some logical test is TRUE or FALSE. For instance, we might want to know if a number is less than 10. In this case, the ‘IF’ function is designed such that if a given number is less than 10, its output is a TRUE result. If a given number is not less than 10, then the output of the ‘IF’ function is a FALSE result.

Excel also allows us to combine multiple ‘IF’ functions into the same formula for a more complex needs. This is the feature that ‘IFS’ improves upon. But more on all that in a bit.

The basics of the ‘IF’ function

Now that we have a bit of background about the ‘IF’ function, let’s look at the syntax:

‘=IF(logical_test,[value_if_true],[value_if_false])’

The items inside the parentheses are what we refer to as arguments. As you can see, the ‘IF’ function has three arguments. The first argument is fundamental to the function. It is the logical test by which we are seeking a TRUE or FALSE result.

In our previous example, our ‘logical_test’ is ‘5 < 10’ if the number we were testing was ‘5’. For our next two arguments, we can choose what we would like for the function to return in the case that the number we are testing is actually less than 10. In this case, we will keep things simple and actually use “TRUE” and “FALSE”as results (we could have easily chosen “Yes” and “No”, or “1” and “0”, etc.) .

So now let’s take a look at this in an actual worksheet:

picture-1

This is the example we just discussed and as you can see in the formula bar, we have chosen the text strings “TRUE” and “FALSE” for our ‘value_if_true’ and ‘value_if_false’ arguments, respectively. Note that if we use a string for these arguments, they must be enclosed in double quotes.

The IF function can be combined with several other functions to increase its functionality. See this video on how to use the functions: IF, IFERROR, OR, AND, TRUE, FALSE and NOT.

Now let’s take a quick look at an example of two ‘IF’ functions in the same formula, often referred to as nested ‘IF’ statements:

picture-2

Take special notice that the second ‘IF’ function actually serves as the ‘value_if_false’ argument of the first ‘IF’ function. Based on our previous example, it should be clear that if the given number we are testing is less than 10, this nested ‘IF’ formula would output ‘less than 10’ and be done. However, in this case, where 11 is the number being tested, the formula is set up so that the false result for the first ‘IF’ test is now the second ‘IF’ test and it will now result in a TRUE result, or ‘less than 20’.

The nested ‘IF’ idea can be expanded up to seven ‘IF’ functions. While that can be powerful functionally, it’s not difficult to imagine how that could become unmanageable at some point from a testing and troubleshooting perspective.

Experts have devised all kinds of workarounds to the nested ‘IF’ function for many years due to its limitations. However, with the release of Excel 2016, Microsoft has now come up with a better solution: the ‘IFS’ function.

How to use the ‘IFS’ function in excel

The ‘IFS’ function is Microsoft’s answer to the nested ‘IF’ function. It allows for the same functionality in a simpler form designed for multiple conditional tests.

The ‘IFS’ function essentially tests multiple conditions until the first true is returned. This may make more sense by looking at the syntax:

‘=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, …)’

So let’s see how this works with the previous scenario:

picture-3

Note the contrast of the formula here compared to the previous example using a nested ‘IF’ formula. For starters, the ‘IFS’ function is a far more straightforward method with improved readability. It is a very intuitive method of achieving the same results.

One thing that might not be obvious at first with the ‘IFS’ function is that its lack of an argument for a FALSE result to any of the logical tests needs to be considered. For instance, in our example, if we were to instead choose the number 21 as our test value, we will get an error if the formula is otherwise left unchanged:

picture-4

So in order to address this, we simply add a last logical test that is always TRUE (like ‘1 = 1’) and select the value we want the formula to return in the event that all previous logical tests turn out to be FALSE.

picture-5

A practical example

Now that we have covered the basics of how to use the ‘IFS’ function, let’s apply it to a real-world example. Let’s consider a situation in which we have a measurement for hand size and we want to convert that to glove sizes. The following table shows our conversion from the measurement to the size.

picture-6 

So the concept here is that the recommended size for hand measurements 6.5 or less is ‘XXS’, 7 or less is ‘XS’, 7.5 or less is ‘S’, and so on. These statements are our logical tests and resulting values if TRUE. And if we have a short list of measurements, we can use the cell references in our arguments to match up to sizes based on the measurement conditions:

picture-7

Note the similarity to our more basic example earlier. This is a great illustration of the ‘IFS’ function at work in a real life type of scenario. We can also replace the literal values in our formula with the cell references in our cross reference table (found two pictures above in the range of A2 to D2):

picture-8

The ‘IFS’ function is just one of the many new features available in Excel 2016 and we have just shown how useful it can be. It builds on one of the most widely used logical functions providing an even broader range of flexibility. With its simpler approach to tackling the needs only nested ‘IF’ functions could previously address, the ‘IFS’ function figures to become a mainstay for even the most inexperienced Excel user. Read more on the use of the IF and IFS functions here.

This guide is written by certified Microsoft Office Specialist Kasper Langmann from Spreadsheeto. Did you like this guide? Then go check out his free Excel training.

3 thoughts on “Excel 2016: IFS Function

  1. Dear Sir

    Please see below screen shot which I trust covers the whole story.

    When I click the command button I want to see both the emails in sent item of outlook 2016.

    Would you be kind enough to give me the VBA code to make this happen?

    TKS & RGDS…BENEDICT

    TRENDYWEAR (PVT) LTD.,

    459/1, KANDY ROAD,

    RANMUTHUGALA,

    KADAWATHA,

    ZIP CODE 11850

    SRI LANKA.

    TEL: +94 (011) 5626601-7 EXT: 247

    TEL: +94 (011) 5444947 (DIRECT)

    FAX: +94 (011) 2972081

    MOBILE: +94 (071) 1219556

Post any comments, feedback or questions below!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s