academy

Categories

AcademyFunctionsUsage & tipsIF

IF function in 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 in Excel

An IF function in Excel consists of 3 components:

  1. A logical test, using an operator sign for logical comparison;
  2. A value to return if the logical test results in TRUE;
  3. A value to return if the logical test results in FALSE.

Format: =IF(A1>B2; “TRUE”; “FALSE”)

Example

We own a green grocery 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"):

Example of how to call the IF function in Excel // PerfectXL Academy

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:

Extend the IF function within a relevant cell range // PerfectXL Academy

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. This way you can test for more than one condition within one function and return more than two results.

Example

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 statement: =IF(B2<20;"Order";IF(B2=>30;"Sale";"No action")):

Example of a nested IF function in Excel // PerfectXL Academy

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:

Extending a nested IF formula in Excel // PerfectXL Academy

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 Risk Finder marks nested IF functions as a risk.