Ways To Use IF Statement & Nested If In Excel _Guide

Nested If In Excel

Today we are going to discuss If & Nested If In Excel. Let’s get started! The One main function of excel is that I use quite a bit in my formulas is the IF function. This IF function is used to test a logical condition. You can also produce two different results that will also depend on it. Check whether the logical condition returns to TRUE or FALSE.

Also See: iPhone Group Text Messaging: How to Send SMS to 100 People just for Price of 1

About IF Function with Single Condition:

You can also consider a scenario where you will need to calculate the Commission Fee for each sales row. This will also depend on where the sales are made (Column D). In case, if the sales were made in the USA. Then the Commission Fee is 10%. If not, then the remaining locations will have a Commission Fee of 5%.

So, This first formula that you will need to enter on Cell F2 is as shown below:

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

The Formula breakdown:

  • The =IF( – So, “=” indicates the beginning of a formula in the cell. And IF is the excel function that we are using.
  • A D2=” USA” – The Logical test that we perform (i.e. if data in column D2 in the USA).
  • An E2*10% – a Result that will be returned by the formula if the initial logical test results in TRUE (i.e. A value in column D2 in the USA).
  • The E2*5% – The Result that will be returned by the formula. In case, if the initial logical test results in FALSE (i.e. then the value in column D2 is NOT USA).
  • ) – The Closing bracket indicating the end of the formula.

After that, you can also copy down the formula from Cell F2 to the rest of the rows in Column F. So, this will also calculate the Commission Fee for each line. So, either by the 10% or 5%. It will also depend on whether the IF logical test returns TRUE or FALSE on each row.

Also See: Command Line Cheat Sheet Wallpaper: Way to Learn the Commands with Background image

The IF Function with Multiple Conditions -Nested If In Excel:

So, What if the rules were a bit more complicated where you need to test for more. Now, this also one logical condition with different results. This will also return for each condition?

Then Excel also has an answer to this! Now, We can combine multiple IF functions within the same cell. Also, there is sometimes that is known as a Nested IF. Then you can also consider a similar scenario where the Commissions are different. This is for each Sales Location as below:

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

Using the Cell F2 (which later will be copied to the rest of the rows in the same column F). You can also enter the formula as follow:

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

Nested If In Excel

Formula breakdown:

  1. An =IF( – Beginning of the formula using an IF statement
  2. The D2=” USA” – The First logical test that we perform (i.e. if data in column D2 in the USA).
  3. An E2*10% – A Result that will also return by the formula if the initial logical test results in TRUE (i.e. value in column D2 in the USA).
  4. Then IF(D2=” Australia”, E2*5%, E2*2%) – A second Excel IF statement that will be assessed if the initial logical test resulted in FALSE (i.e. value in column D2 is NOT USA). So, This is a similar syntax of “IF Function with Single Condition” discussed earlier in this article. Now where if the value on Cell D2 is Australia, then the result of E2*5% will be returned. Then if the value is not Australia, the function will return the result of E2*2%.
  5. A ) – Closing bracket indicating the end of the formula for the first IF function.

Also See: What is the meaning of this Error “Ethernet Doesn’t Have a Valid IP Configuration”

Details About nested if In excel:

So, Excel will assess the formula from the left to the right. If a logical test is met (e.g. D2=“USA”. Then the function will stop and also return the result. You can also ignore any of the further logical tests after (e.g. D2=“Australia”.)

In case, if the first logical test returns FALSE (i.e. location is not the USA. Then it will also continue to assess the second logical test. In case, if the second logical test returns FALSE as well (i.e. location is not Australia). Then we do not need to test further as we know the only possible value on Cell D2 is Singapore. However, it should return a result of E2*2%.

In case, if you prefer clarity, then you can also add the third logical test IF(D2=” Singapore”, “value if TRUE”, “value if FALSE”). So, the full extended formula is as shown below:

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

So, as we mentioned earlier, then the above will also return the same result as the initial formula that we had.

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

Some Tips

  • Now, This is For every single IF( function, there needs to be an opening and closing round bracket. If there are three IF functions as per one of the examples above. Then the formula will need three closing brackets “)))”, in each marking and also the ending of a corresponding opening IF( statement.
  • In case, if we do not specify the second outcome of the logical test. If the logical test resulted in FALSE). Then the default value assigned by Excel will be the text “FALSE”.  A formula =IF(D2=” USA”, E2*10%) will return the text “FALSE” if D2 is not “USA”.
  • Also, if you have several different logical tests in each with its own different outcome. Then you can also combine/nest in the IF function multiple times. This is one after another this is similar to the example above.

Also See: How To Fix If Chrome Freezing, Crashing & Not Responding?

Conclusion:

So, this is all about IF & nested if In excel. The One main function of excel is that I use quite a bit in my formulas is the IF function. You can also produce two different results that will also depend on it. Check whether the logical condition returns to TRUE or FALSE. Hope you like this article! If you have any questions then let us know in the comments below.

Leave a Reply

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