How to use the IF Function
An IF function in Excel consists of 3 components:
- A logical test, using an operator sign for logical comparison;
- A value to return if the logical test results in TRUE;
- A value to return if the logical test results in FALSE.
Format: =IF(A1>B2; “TRUE”; “FALSE”)
We own a greengrocery and we have a list of fruits in stock. Whenever the amount in stock is less than 20, we need to place a new order with the wholesaler. The IF function can help us with this task. IF the amount of a certain fruit is under 20 (TRUE), we need to place an order. IF the amount is 20 or more (FALSE), we don’t have to take any action.
We call the function in the ‘status’ column: =IF(B2<20;”Order”;”No action”):
When we press enter, the function will publish the result for FALSE: No action, because the value in cell B2 isn’t less than 20.
Next, we extend the IF function to the relevant cell range within the ‘Status’ column, to see the result for each fruit:
As a result we can see exactly which fruits we need to re-order.
Nested IF Function
It is possible to use an IF statement as a TRUE or FALSE value inside another IF function. This way you can test for more than one condition within one function and return more than two results.
We will use the same list of fruits, but this time we will re-order when the amount is less than 20 and we will put the fruits on sale when the amount is 30 or greater than 30.
In this case our value for FALSE will be replaced by a second IF function: =IF(B2<20;”Order”;IF(B2=>30;”Sale”;”No action”)):
Pressing enter will display the value in cell C2. We will extend the function to the relevant cell range in column C, to find out which fruits need to be re-ordered, which fruits need to be put on sale and which fruits require no further action:
Nested IF functions are Risky
A spreadsheet developer can nest up to 64 IF statements within one formula! So you can imagine that the overview can become very unclear and complex. This is why PerfectXL marks nested IF functions as a risk.