One 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 TRUE or FALSE.
Let’s use the mobile phone sales table below as an example. You can download the example file here .
Consider a scenario where you need to calculate the Commission Fee for each sales row, depending on where the sales was made (Column D). If the sales was made in the USA, the Commission Fee is 10%, otherwise the remaining locations will have Commission Fee of 5%.
The first formula that you need to enter on Cell F2 is as shown below:
Thenyou can copy down the formula from CellF2 to the rest of the rows in ColumnF and it will calculate the CommissionFee for each line, either by 10% or 5% dependent on whether the IF logical test returns TRUE or FALSE on each row.
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 multiple IF functions within the same cell, which is sometimes known as a Nested IF.
Consider a similarscenario where the Commissions aredifferent for each Sales Location asbelow:
In Cell F2 (which later will be copied to the rest of the rows in the same column F), enter the formula as follow:
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 returns FALSE (i.e. location is not USA), it will continue to assess the second logical test. If the second logical test returns FALSE as well (i.e. location is not Australia), we do not need to test further as we know the only possible value on Cell D2 is Singapore hence it should return a result of E2*2%.
If you prefer for clarity, you can add the third logical test IF(D2=”Singapore”, “value if TRUE” , “value if FALSE”). Therefore, the full extended formula is as shown below:
Asmentioned earlier, the above will return the same result as the initial formulathat we had.