IF statement is used to test whether a certain condition is met. Sometimes you can have more than one condition to test thus creating an If statement within another if statement (nested if statement). This enables you to come up with if statement with many possible outcomes as you want.
The excel If () function is used when evaluating a condition with two outcomes. e.g. (evaluate if sales are greater than 1000 and give different values for each outcome). The function is commonly used to evaluate whether a condition is true or false.
It returns one outcome if the logical; test is TRUE and another outcome if the condition is FALSE.
Syntax for two outcomes
IF(logical_test, [value_if_True], [value_if_False])
There are situations where you have to work with more than two outcomes. In such situation, you're required to create a Multiple IF statements or Nested If Statement.
How to use multiple if statement with more than two outcomes
This multiple if statement works by replacing one of the True/False calculation with another if function. You create if function within another if function.
=IF (CONDITION X, OUTPUT B, IF (CONDITION Y, OUTPUT C, OUTPUT D)))
Example: Commission for sales based on monthly sales
Open your excel sheet and enter the following details
From the above table, create an if statement with the following conditions
|Less than $400||7%|
|Sales between $400 and $800||12.5%|
|Sales above $800||16%|
Use nested if to calculate the commission for Shelly.
Perform a logical test on sales: Is Sales figure less than $400? If TRUE, then calculate commission. If FALSE, then is the sales less than $800? If TRUE then calculate commission and if False, then calculate the commission.
Enter the following formula in cell C2
Press enter key to calculate the commission for Shelly.
Use Autofill feature to copy the formula to the rest of sales team.
In the above example, the If statement checks if the cell B2 is greater than less than $400. If that is true, it calculates the commission with 7% and stops calculating. If cell B2 had more than $400 and sales of less than $800, the commission will be calculated at 12.5% otherwise if the sales figure is greater than $800 commission will be calculated at a rate of 16%.