IF Function Excel
The IF function in Excel performs a logical comparison between two values. The result of the IF function is either TRUE or FALSE. For example, we can test if the value in cell B2 is greater than the value in cell A2. If so, the result is TRUE, if not, the result is FALSE.
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:
Now we can see exactly which fruits we need to re-order.
Nested IF Function
It is possible to use an IF function as a TRUE or FALSE value inside another IF function. This way you can test for more conditions 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. Next we 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 functions within one formula! So you can imagine that nested IF statements can become very unclear and complex. This is why PerfectXL marks nested IF functions as a risk.
Try PerfectXL Now
PerfectXL provides full automatic clarity about all risks, mistakes and other problems in your spreadsheets, plus an extensive improvement plan. You don’t have to be an expert yourself and you don’t need much time to fix your file.Learn about the features
Prefer INDEX and MATCH over VLOOKUP
VLOOKUP is used a lot more often than the INDEX and MATCH combination, while the latter are really much better. INDEX and MATCH are less error prone and a lot more efficient.
INDEX & MATCH!