How to Use If and Nested If Statements in Excel

Calculate commissions and so much more

One Excel function that I use quite a bit in my formulas is theIFfunction. TheIFfunction is used to test a logical condition and produce two different results depending on whether the logical condition returnsTRUEorFALSE.

Let’s use the mobile phone sales table below as an example. You can download theexample file here.

IF Function

with Single Condition

Consider a scenario where you need to calculate theCommission Feefor each sales row, depending on where the sales was made (Column D). If the sales was made in theUSA, theCommission Feeis 10%, otherwise the remaining locations will haveCommission Feeof 5%.

The first formula that you need to enter onCell F2is as shown below:

Formula breakdown:

Then you can copy down the formula fromCell F2to the rest of the rows inColumn Fand it will calculate theCommission Feefor each line, either by 10% or 5% dependent on whether theIFlogical test returnsTRUEorFALSEon each row.

IF Function with Multiple Conditions

What if the rules were a bit more complicated where you need to test for more than one logical condition with different results being returned for each condition?

Excel has an answer to this! We can combine multipleIFfunctions within the same cell, which is sometimes known as aNested IF.

Consider a similar scenario where theCommissionsare different for eachSales Locationas below:

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

Formula breakdown:

As Excel will assess the formula from the left to the right, when a logical test is met (e.g.D2=“USA”,the function will stop and return the result, ignoring any further logical test after (e.g.D2=“Australia”.)

So if the first logical test returnsFALSE(i.e. location is notUSA), it will continue to assess the second logical test. If the second logical test returnsFALSEas well (i.e. location is notAustralia), we do not need to test further as we know the only possible value onCell D2isSingaporehence it should return a result ofE2*2%.

If you prefer for clarity, you can add the third logical testIF(D2=”Singapore”, “value if TRUE” , “value if FALSE”). Therefore, the full extended formula is as shown below:

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

Quick Tips

Founder of Help Desk Geek and managing editor. He began blogging in 2007 and quit his job in 2010 to blog full-time. He has over 15 years of industry experience in IT and holds several technical certifications.Read Aseem’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved