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:
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.
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:
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.
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:
For example, to apply an 18% discount, you would use:
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:
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 granula discounts based on a lookup operation.