IF with other calculations

The goal is to demonstrate how other formulas and functions can be nested inside the IF function. The example is a simple quantity-based discount formula.

IF function

The IF function evaluates a logical test and returns one value if the result is TRUE, and a different value if the result is FALSE. The generic syntax for IF looks like this:

=IF(logical_test,value_if_true,value_if_false)

 For example, if cell A1 contains the value 75, then you could use IF to return "Pass" or "Fail" like this:

=IF(A1>70,"Pass","Fail") // returns "Pass"

If the value in A1 is 65, then the same formula will return "Fail":

=IF(A1>70,"Pass","Fail") // returns "Fail"

What is not obvious with IF is that the logical_test, the value_if_true, and the value_if_false can all be other formulas. The example below shows how this works.

Example

In the worksheet shown, the goal is to apply a simple quantity-based discount to the total calculated in column E. If the quantity is greater than 20, we want to discount the total by 10%. Otherwise, we want to calculate the total normally. In cell E5, the formula used to perform this task is:

=IF(C5>20,C5*D5*0.9,C5*D5)

The formula works like this:

• The logical test is C5>20. This checks if the quantity of items sold (in cell C5) is more than 20.
• The value if true is C5*D5*0.9. This calculates the total price as the quantity sold times the price per item and then applies a 10% discount by multiplying the result by 0.9.
• The value if false is C5*D5. This calculates the total price as the quantity sold times the price per item, without any discount.

So, if more than 20 items were sold, the formula applies a 10% discount to the total price. Otherwise, it just calculates the total price without any discount. You can then copy the formula down column E to apply it to all items in the spreadsheet.

Other calculations

The calculations used inside the IF function can be customized as needed. For a more general formula that applies the discount itself, you can use the following:

=IF(C5>20,C5*D5*(1-discount),C5*D5)

For example, to apply an 18% discount, you would use:

=IF(C5>20,C5*D5*(1-18%),C5*D5)

This works because Excel will automatically evaluate the percentage 18% as the number 0.18. You can also adjust calculations in the logical test as needed. To apply a 20% discount to apples only, you could use the AND function in the logical test like this:

=IF(AND(C5>20,B5="apples"),C5*D5*(1-20%),C5*D5)

In Excel, nesting other calculations inside a function or formula is a common practice in many more advanced formulas. You can find many examples in this list.

Detailed quantity-based calculation

If you are interested in a more detailed example of a quantity-based discount formula, this example uses XLOOKUP instead of IF to apply more granular discounts based on a lookup operation.