Let’s take a look at IF, Nested IF and IFS

Let’s take a look at IF, Nested IF and IFS

One of the most commonly used functions in Excel is the ‘IF’ function.
IF-function-excel

Its usefulness and application are quite broad due to its simplicity.

It is a logical function that basically allows the user to specify results based on whether or not a specific criterion is met.

The result hinges upon either a true or false outcome of a logical test. Think of it in terms of ‘if this is true, then that – otherwise (if ‘this’ is false) something else’.

Obviously, this is very simple and straightforward yet it is incredibly powerful as well.

What is really the most powerful thing about the ‘IF’ function is that it can be ‘nested’ within and combined with other functions to create an incredible amount of flexibility to meet different requirements.
Kasper Langmann, Co-founder of Spreadsheeto

But this is not to say that the ‘IF’ function is limited to these possibilities because like many Excel functions, it is only limited to user’s ability to think analytically and creatively.

For this particular article, all the examples will be performed in Excel 2016 for Windows.

Follow along by downloading our exercise file right below!

Click to Download This Tutorial’s FREE Sample File

True or false

The ‘IF’ function is a logical function which basically means its results are based on one of two outcomes: true or false.
true or false

The user sets the criteria by which the function evaluates whether it is indeed true or false.

Then the function allows us to dictate what it returns in either of those cases!

Like any other function, the ‘IF’ function needs some input in order to work properly. These inputs we call ‘the syntax’.
Kasper Langmann, Co-founder of Spreadsheeto

The syntax of the ‘IF’ function is so simple making its concept fairly easy to grasp:

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

  •  The ‘logical_test’ is the criteria by which we want our function to return a result based on whether the outcome of the test is true or false. For instance, our ‘logical_test’ might be “0<1”, or “0>1”. We know from simple examination that the former would be true whereas the latter would be false. Keep this in mind for a moment.
  • The ‘value_if_true’ and ‘value_if_false’ arguments need little explanation. These are simply values that we want the formula to return based on the outcome of the ‘logical_test’. Notice that these arguments are optional as indicated by the brackets. To clarify, only one or the other is optional at a time. If both arguments are omitted, the function will return a value of ‘0’.

True or false (advanced example)

Let’s take a look at a more complex example.

true or false example

We have a ‘Data’ table of 6 rows and 3 columns where each cell contains a unique value.

The ‘Analysis’ table shows our formulas using the ‘IF’ function in the ‘Formula’ column.

The results are in the ‘Result’ column which contains the actual live formula.

There is also a ‘Boolean Result’ column that shows the ‘TRUE’ or ‘FALSE’ result of the evaluation of the function. This simply indicates which of the value arguments gets returned by the function itself, the ‘value_if_true’ or the ‘value_if_false’. Note that in test 3, the ‘logical_test’ of ‘A6=15’ is true. Therefore, the result is the value in ‘A6’, or 15.

On row 6 the ‘logical_test’ argument, ‘A6=A5’, evaluates to false and therefore the formula returns ‘other’ since that is the value of the ‘value_if_false’ argument

These are a few examples to show how the ‘IF’ function can be supplied with literal values, cell references, strings, and even the Boolean value ‘TRUE’ and ‘FALSE’.
Kasper Langmann, Co-founder of Spreadsheeto

Testing and validating numeric data

The great thing about the ‘IF’ function is that it allows us to test large sets of data quickly.

validating numeric data

Let’s say we had a long list of values that could be a dollar amount for sales and we wanted to check the data to quickly check above and below a threshold of say, 200.

Then we could set up a cell to be our threshold value and use that cell reference within the ‘logical_argument’.

That is exactly what we have done in the following example.

We have set the formula up so that no value is returned if the test value is greater than our threshold value but if it is below the threshold, it will return ‘Below Threshold’.

We can see through just a few examples how useful and indispensable the ‘IF’ function can truly be.

However, what really makes this function so incredibly powerful is the ability to nest them inside of each other. This ability to nest functions is true for other functions within an ‘IF’ function as well as ‘IF’ functions within other functions.
Kasper Langmann, Co-founder of Spreadsheeto

But we will keep it fairly simple and stick with ‘IF’ functions nested within other ‘IF’ functions.

Nesting the ‘IF’ function

Nesting ‘IF’ functions is especially useful when another ‘IF’ logical needs to be addressed as the result of the outcome of either the ‘value_if_true’ or ‘value_if_false’ arguments.
nesting

For example, if we look to our previous example with the ‘200’ threshold as our logical test, we might want to take the formula a step further and test the values to isolate any that would be above ‘250’.

Then we could write our formula to be:

‘=IF(A17>B14, IF(A17>250,””, ”Less than 250”),”Below Threshold”)’

So, in this case, our ‘value_if_true’ for the first ‘IF’ functions becomes an ‘IF’ function that tests the same cell for whether it is greater than 250 or not.

Now there are three possible outcomes to our formula rather than just two.

This could expand further to even split out the ‘value_if_false’ argument to some more granular test.

Then our formula would have four possible outcomes.

Then we could go even further and replace the same arguments within the new ‘IF’ functions and create more possible outcomes.

But as extremely powerful as this is, it definitely becomes difficult to manage as the formula grows.
Kasper Langmann, Co-founder of Spreadsheeto

Example: Nesting the ‘IF’ function

Let’s take a look at a real example that demonstrates the use of nested ‘IF’ functions before we move on to something new that Excel offers as a more streamlined approach.

In this example, we have a list of colors in the data range we are testing. But of all the colors listed we only want to know when we have a match to 4: green, blue, red, or purple.
Kasper Langmann, Co-founder of Spreadsheeto

If values in our data set are not found within this list of 4 colors, we will assign it the value ‘other’.

Notice in the nested ‘IF’ above that we have added and ‘IF’ function as the ‘value_if_false’ argument for three different previous ‘IF’ functions.

What we have essentially done is ask Excel to go down our list of 4 colors and test the value of the cell reference ‘A7’ until it either finds one of those colors or not.

This is a very powerful feature of the ‘IF’ function.

However, it’s not difficult to see how this becomes rather difficult to manage as we add more nested ‘IF’ functions to the mix.

Troubleshooting can become somewhat of a nightmare as we nest more functions.

With this in mind, we now turn our attention to a brand new function that Excel offers in order to further reduce this problem. This solution is a function called ‘IFS’!
Kasper Langmann, Co-founder of Spreadsheeto

Nested ‘IF’ rethought: Here’s IFS

The ‘IFS’ function simply expands on the idea of nested ‘IF’ functions.
new

However, it simplifies things by allowing us to place a number of logical tests and subsequent value if that logical test is true without the additional need to supply a value if the test is false.

Let’s just take a look at the syntax to clarify:

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

Notice that we no longer need to nest a new ‘IF’ function into the ‘value_if_false’ argument of the previous ‘IF’ in order to add a new logical test.

With the ‘IFS’ function we can just add as many tests and values if true arguments up to a maximum of 127. This adds an incredible amount of efficiency and removes much of the complexity that comes with nesting ‘IF’ functions.
Kasper Langmann, Co-founder of Spreadsheeto

So let’s take a look at what an ‘IFS’ function would look like if we use it in place of the nested ‘IF’ function in our previous example where we tested for our list of 4 colors.

While our new formula may not seem any shorter in length, it is clear by examination that it is far simpler and easy to read.

One very important thing to realize about the ‘IFS’ function is that it has no ‘value_if_false’ argument. This means that we have to compensate by creating a ‘Something is True’/’Value if True’ pair that achieves the same goal.
Kasper Langmann, Co-founder of Spreadsheeto

Notice in the previous figure where our last ‘Something is True’/’Value if True’ pair that we have used ‘A7<>”Purple”’ as our last ‘Something is True’ argument.

The ‘IFS’ function essentially goes through the list of supplied ‘Something is True’/’Value if True’ argument pairs until it finds a [true] match.

This is why we have to develop a pair that creates a solution for those cases in which our test value will not match the previous ‘Something is True’/’Value if True’ pairs.

In our example, we simply used a statement that said if the value in A7 did not equal ‘Purple’, then return the value ‘Other’. Note we only need to test for one color of the list at this point because it is only relevant if the value in ‘A7’ had already failed the previous tests.

Let’s take a look at another example using ‘IFS’. In this scenario, we have sales figures that we want to test and then apply a bonus amount accordingly. We have a progressive bonus schedule that shows the payout based on a sales quota amount.
Kasper Langmann, Co-founder of Spreadsheeto

So our final formula will test our sales amounts against five different quota amounts and return the appropriate bonus.

We could certainly nest ‘IF’ functions to achieve our goal and it could look something like this:

Note that there is a ‘$’ in some of the cell references and for anyone unfamiliar with this, it is simply a method to lock either the row, column, or both, of the cell reference so they do not change as we copy the formula to all rows for the test data.
Kasper Langmann, Co-founder of Spreadsheeto

But the main thing to notice with this formula is the number of closing parentheses at the end of it.

Clearly, as we add more ‘IF’ functions to the existing formula, things get very complicated and difficult to manage.

Let’s take a look at how to achieve the exact same thing using the ‘IFS’ function as an alternative to the nested ‘IF’ approach.

Notice again like in our previous example how the formula is set up to test each quota level until it progresses the last argument pair.

In this case, the sales amount will cause the formula to return the ‘Value if True’ argument for the last pair that it matches as true. This example may seem a bit confusing as you review the formula, so let’s walk through it.
Kasper Langmann, Co-founder of Spreadsheeto

Notice that the first argument pair tests to see if the value in cell A2 is less than the value in E2, which is $250.

If this evaluates to true, the formula should return a value of ‘0’.

This is because unless the sales value is at least $250, there is no bonus paid out.

The next step of the formula tests to see if the value in A2 is less than the value shown in E3, which is $500.

bonusIf it is less than $500 but it did not test true as less than $250, the formula is designed to return the value in cell F2, which is $25.

This simply means that if the sales amount is greater than $250 but less than $500, the bonus should be $25.

The next few argument pairs work in the same fashion until we get to the final pair.

If our sales amount has not evaluated true for all argument pairs up to this point, that leaves us with one last test.

Since $1,250 this is our last quota value to test against, we do not have a subsequent value to evaluate the sales amount as being less than.

Therefore, if the sales amount has not been less than any of the quota amounts up to the value of $1,250 in cell E5, it must be greater than that value and our task is to tell our ‘IFS’ function how to evaluate that.

Now we simply ask the formula to test if the sales amount value is greater than E6 and to return the bonus value in F6.

See in the next figure that both the ‘IF’ and ‘IFS’ formulas evaluate to the same results.

Rounding up on nesting…

Obviously, the ‘IFS’ function provides a much clearer and more efficient alternative to nested ‘IF’ functions.

However, for anybody who has used nested ‘IF’ functions in the past, it may take some getting used to the syntax due to the absence of any ‘value_if_false’ argument. There is little doubt, however, that the function offers a more streamlined approach to the same problems providing a more elegant solution.
Kasper Langmann, Co-founder of Spreadsheeto

Combining ‘IF’ with ‘AND’

Another very useful way to expand the usefulness of the ‘IF’ function is to combine it with the ‘AND’ function. This is especially relevant when we have multiple ‘logical_test’ requirements to evaluate.

The logic is simply like saying “IF this AND this are true, then do this but IF this AND this are not all true, do something else.”
Kasper Langmann, Co-founder of Spreadsheeto

If we look into the syntax of the ‘AND’ function, we see that it allows us to insert more than one logical argument to be tested:

Note in the figure above that inserting the ‘AND’ function inside parentheses after the ‘IF’ now allows us to insert multiple logical arguments that all must be met for an outcome of true.

This means that we can keep adding as many logical arguments as you would like met for a true outcome (up to 255 maximum).

Now we will look at a scenario where using the ‘IF’ with the ‘AND’ would be very useful:

Let’s assume we run a business in which we offer customers a discounted price based on quantities purchased. We can afford to offer these discounts because our inventory costs also decrease when we purchase in bulk from our wholesalers. By passing on the savings to our retail customers we are able to drive higher sales and production.
Kasper Langmann, Co-founder of Spreadsheeto
  • Our non-discounted retail price is $25 per product.
  • However, if the customer purchases 100 or more units in an order, the discount off the retail price of $25 is 10%.
  • To provide an incentive to our customers to buy in even greater quantities, we discount the price by 25% for all orders of 500 or more.

We need a solution that calculates the final price of our customers’ orders while considering any discounts due them based on their order quantity.

To simplify this example a bit, we will first break down our eventual formula by 3 different logical tests.

Test 1: If ‘Purchase Qty’ is greater than 100

Test 2: If ‘Purchase Qty’ is less than 500

Test 3: If ‘Purchase Qty’ is greater than 500

We then build separate ‘IF’ functions for each in order to return either a ‘TRUE’ or ‘FALSE’ based on the ‘Purchase Qty’ on each row of data.

We will then plug in the cell references that contain these results into our larger formula for the sake of simplicity.

For example, the formula and result for Test 1 on row 10 is illustrated in the next figure.

Cell ‘B5’ refers to the minimum quantity the customer must purchase in order to qualify for a 10% discount, or 100.

Test 2 will refer to the upper maximum for quantities qualifying for 10% and then Test 2 will refer to the minimum quantity to qualify for the 25% discount.

Notice that we simply use ‘TRUE’ and ‘FALSE’ as our ‘value_if_true’ and ‘value_if_false’ arguments for the sake of simplicity.

All ‘TRUE’ values in the test columns are bolded for the sake of emphasis.

Each cell in our test columns evaluate to either a ‘TRUE’ or ‘FALSE’ value making it easier for us to validate the results of our eventual nested formula.

All of our logical test formulas get us the following results. You can test each one based on the definition of the three tests above and see that the true and false values on each row correlate.

These results can now be inserted into our larger formula combining ‘IF’ with ‘AND’ as well as inserting a second ‘IF’ function as the ‘value_if_false’ argument of the initial ‘IF’ function.
results

Specifically, the first ‘IF’ combined with the ‘AND’ tests for the results of Test 1 and Test 2, and the ‘value_if_true’ (remember, in this case that both Test 1 and Test 2 must evaluate to ‘TRUE’) is ‘D5’ which is $22.50 (the discounted price for orders of 100 or more but less than 500).

The ‘value_if_false’ is actually another ‘IF’ function that evaluates or Test 3 result. If this result is ‘TRUE’, the function returns the value in ‘D6’ which is $18.75 (the discounted price for orders of 500 or more). If this ‘IF’ function evaluates to ‘FALSE’ it will return the value in ‘D2’, or $25.00.

Putting all this together, the formulas and results are shown in the following figure.

One thing to note is that we do not need to set our cell references for our logical test arguments in the ‘AND’ function to equal ‘TRUE’ or ‘FALSE’. The formula evaluates this based on the content of the cell references themselves.
Kasper Langmann, Co-founder of Spreadsheeto

This can be validated by substituting the formulas contained in those cell references into our larger formula in place of the cell references themselves.

Before we look at the actual resulting formulas let’s quickly review the cell references and their equivalent formulas for substitution.

D5: ‘=D2*(1-C5)’ where C5=.10, or 10%. Therefore, the long version of this formula will actually be ‘=D2*(1-.10)’.

D6: ‘=D2*(1-C6)’ where C6=.25, or 25%. Like the previous formula, the long version of this one becomes ‘=D2*(1-.25)’.

Refer to Figures 9 and 10 for a quick review of the formulas to substitute for the cell references in the Test 1, Test 2, and Test 3 columns.

When all is said and done, the formulas shown in Figure 11 (column G) will now look like the following:

While this form of the formula may look very daunting and complicated at first glance, if we simply walk through it syntactically, it actually follows pretty simple logic.
Kasper Langmann, Co-founder of Spreadsheeto

Furthermore, the Excel tooltip box that appears as we input our formula is an invaluable resource when working with longer formulas such as this.

As we begin to type our formula, the tooltip keeps our syntax on track as it highlights our arguments in bold font as we go along.

Conclusion: Taking IF further

toolsThere are many other options for leveraging the ‘IF’ function.

Another very common function to combine with the ‘IF’ function is ‘OR’. In contrast to the ‘AND’ function, combining ‘OR’ with ‘IF’ evaluates one or the other of two logical tests for a true or false outcome.

We could also combine ‘IF’ with ‘ISBLANK’ in order to return values based on whether or not a cell is blank.

These examples are truly just the tip of the iceberg when it comes to the possibilities. Like many other functions, the ‘IF’ function lends itself well to being inserted into other more complex functions like the ‘VLOOKUP’.
Kasper Langmann, Co-founder of Spreadsheeto

Furthermore, since the ‘IF’ function is simply a logical test, other complex functions can be used as the ‘value_if_true’ and ‘value_if_false’ arguments for further automation and efficiency within worksheets and analyses.

Like all Excel functions, the possibilities are numerous with a little creativity and ingenuity and as we have seen, logical tests like ‘IF’ are extremely powerful in their simplicity.

CLICK HERE to try our free Excel training.

2017-02-28T08:45:48+00:00

Send this to friend