Using if and nested If statements in Excel

An Excel function that I use quite a bit in my formulas is the IF function. The IF function is used to test a logical condition and produce two different results depending on whether the logical condition returns WHERE or FALSE

Let’s use the cell phone sales table as an example. You can download the sample file here.

IF function with single condition

Consider a scenario where you need to calculate the Commission for each sales line, depending on where the sale was made (Column D† If the sale was made in the United Statesthe Commission is 10%, otherwise the other locations Commission of 5%.

The first formula to enter on cell F2 is as shown below:

=IF(D2="USA", E2*10%, E2*5%)

Formula breakdown:

  1. =IF( – The indicates the beginning of a formula in the cell and IF is the Excel function we use.
  2. D2=”US” – Logic test we run (ie as data in column) D2 is United States
  3. E2*10% – Result returned by the formula if the first logical test results in WHERE (ie value in column D2 is United States
  4. E2*5% – Result returned by the formula if the first logical test results in FALSE (ie value in column D2 is NOT United States
  5. – Close parenthesis indicating the end of the formula.

Then you can copy the formula from cell F2 to the rest of the rows in Column F and it calculates the Commission for each line, either by 10% or 5%, depending on whether the IF logic test returns WHERE or FALSE on every row.

IF function with multiple conditions

What if the rules were a little more complicated, requiring you to test for more than one logical condition, returning different results for each condition?

Excel has an answer to this! We can combine several IF functions within the same cell, also known as a nested IF

Consider a similar scenario where the committees are different for each Sales location as below:

  • United States 10%
  • Australia 5%
  • Singapore 2%

In cell F2 (which will later be copied to the rest of the rows in the same column F), enter the formula as follows:

=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,E2*2%))

Formula breakdown:

  1. =IF( – Start of the formula with an IF statement
  2. D2=”US” – First logical test we run (ie as data in column) D2 is United States
  3. E2*10% – Result returned by the formula if the first logical test results in WHERE (ie value in column D2 is United States
  4. IF (D2 = “Australia”, E2 * 5%, E2 * 2%) – second Excel IF statement that is evaluated if the initial logic test resulted in FALSE (ie value in column D2 is NOT United States† This is a similar syntax of “IF function with single condition” discussed earlier in this article where as value at Cell D2 is Australiathe result of E2*5% will be returned. Otherwise, if the value is not Australiathe function returns the result of E2*2%.
  5. – Close parenthesis indicating the end of the formula for the first IF function.

Since Excel evaluates the formula from left to right, when a logical test is met (e.g. D2=“US”, the function stops and returns the result, taking any further logical test after (e.g. D2=”Australia”

So if the first logic test returns FALSE (ie location is not United States), it will continue to assess the second logic test. If the second logic test returns FALSE also (i.e. location is not Australia), we don’t need to test further because we know the only possible value at cell D2 is Singapore therefore it should return a result of E2*2%

If you prefer clarity, you can add the third logic test IF(D2=”Singapore”, “value if TRUE” , “value if FALSE”)† Therefore, the complete extended formula is as shown below:

=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,IF(D2="Singapore",E2*2%)))

As mentioned before, the above will give the same result as the original formula we had.

=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,E2*2%))

Quick Tips

  • For every single IF( function, there must be a round hook for opening and closing. If there are three IF functions according to one of the above examples, the formula needs three closing parentheses each marking the end of a corresponding opening IF( pronunciation.
  • If we don’t specify the second outcome of the logic test (when the logic test resulted in) FALSE), the default value assigned by Excel is the text “FALSE”. So formula =IF(D2=”US”, E2*10%) will return the text “FALSE” if D2 is not “UNITED STATES”
  • If you have several different logic tests, each with their own different outcome, you can use the . combine/nest IF function several times, one after the other, similar to the example above.

Leave a Reply

Your email address will not be published.