Way to Use If & Nested If Statements in Excel: Tutorial

So an IF statement can have two results. Simply the first result is if your comparison is True, the second if your comparison is False.

The one Excel function that I use quite very little in my formulas is the IF function. However, the IF function is used to test a logical condition. Also, enhance two different results that depending on whether the logical condition returns Ture or False.

So let’s use the mobile phone sales table mentioned-below as an example.

Nested If Statements

IF Function with just the Single Condition

Here you have to suppose that a scenario where you have to calculate the Commission Fee for each sales row. Depending on where the sales were launched (Column D). Whether the sales were created in the USA, the Commission Fee is 10%. Otherwise, the remaining locations will have a Commission Fee of 5%.

Therefore the first formula that you have to enter on Cell F2 is mentioned below:

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

Formula breakdown:

  • =IF( – The “=” specifies the starting of a formula in the cell. And IF is the excel function that we are using.
  • D2=” USA” – The logical test that we perform. That is whether data in column D2 in the USA).
  •  E2*10% – Consequently that will be come back by the formula if the initial logical test results in TRUE (i.e. value in column D2 in the USA).
  • E2*5% – Result that will also come back by the formula if the initial logical test results in FALSE (i.e. value in column D2 is NOT USA).
  • ) – A closing bracket that specifies the end of the formula.

Although when you are able to copy down the formula from Cell F2 to the rest of the rows in Column F. And it is also able to evaluate the Commission Fee for each line. Either by 10% or 5% dependent on whether the IF logical test returns TRUE or FALSE on each row. (Use If & Nested If Statements in Excel)

Also read: How to Open .pages file in Windows 10

Note: If you are going to use text in formulas, you have to wrap the text in quotes (e.g. “Text”). The only exception to that is using TRUE or FALSE, which Excel automatically understands.

IF Function with Multiple Conditions

What whether the rules were a bit difficult where you have to test for more than one logical condition with various consequences being come back for each condition?

However, Excel has an answer to this! We are able to join the multiple IF functions within the same cell. Which is sometimes known as a Nested IF.

Now you have to consider a similar scenario where the Commissions are different for each Sales Location as mentioned below:

  • USA 10%
  • Australia 5%
  • Singapore 2%

Similarly, in Cell F2 (that will be copied to the rest of the rows in the same column F), so here you have to enter the formula as mentioned below:

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

Formula breakdown:

  • =IF( –  In the starting of the formula use an IF statement.
  • D2=” USA” – First logical test that we do (that is if data in column D2 in the USA).
  •  E2*10% – The consequences that will be come back by the formula. If the initial logical test results in TRUE (i.e. value in column D2 in the USA).
  • IF(D2=” Australia”, E2*5%, E2*2%) – second Excel IF statement that will assess if the initial logical test results in FALSE (i.e. value in column D2 is NOT USA). This is a similar syntax to “IF Function with Single Condition” explained in this article. Where if the value on Cell D2 is Australia, however, the consequences of E2*5% will also come back. Otherwise, if the value is not Australia, then the function will return the result of E2*2%.
  • ) – Closing bracket specifying the end of the formula for the first IF function.

Although Excel will evaluate the formula from the left to the right. Similarly, when a logical test is met (e.g. D2=“USA”, the function will come to an end and return the result. disregarding any further logical test after (e.g. D2=“Australia”.)

So if the first logical test returns FALSE (i.e. location is not the USA), it will continue to assess the second logical test. Similarly, whether the second logical test returns FALSE (i.e. location is not Australia). So that we do not have to test more as we know just the possible value on Cell D2 is Singapore. Consequently, it should return a consequence of E2*2%.

Simply whether you recommend for clarity, do then you are able to add the third logical test IF(D2=” Singapore”, “value if TRUE”, “value if FALSE”). Therefore, the completely increased formula is as shown below:

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

As described, the above will return the exact result as the initial formula that we had.

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

Guidelines (Use If & Nested If Statements in Excel)

  • Although for each and every single IF( function, there have to be an opening and closing round bracket. Similarly, when there are three IF functions as per one of the instances above. The formula will require three closing brackets “)))”, each marking the ending of a coincidence opening IF( statement.
  • Therefore, whether we do not identify the second outcome of the logical test. When the logical test resulted in FALSE). So the default value assigned by Excel will just the text “FALSE”. So formula =IF(D2=” USA”, E2*10%) will return the text “FALSE” if D2 is not “USA”.
  • Furthermore, whether you contain various different logical tests each with its own different outcome. So then you are able to combine/nest the IF function multiple times, one after another, as same as the example above.

You can also read this article: How Do I Insert Excel into Word Documents

 Use If & Nested If Statements in Excel-Conclusion

The IF function is one of the most used functions in Excel. The one Excel function that I use quite very little in my formulas is the IF function. However, the IF function is used to test a logical condition. Also, enhance two different results that depending on whether the logical condition returns Ture or False. Well, we hope that you will like this article the most in case of queries you can ask us in the comments below. And we are hope full that this tutorial: Way to Use If & Nested If Statements in Excel will help you a lot.

Leave a Reply

Your email address will not be published. Required fields are marked *