How to Use the IF Function for Discounts in Excel (4 Examples)

Dataset Overview

In the dataset, we have prices of various products as well as a conditional discount percentage.

Example 1 – Discount for Products Exceeding $25

Suppose you have a dataset with product prices, and you want to apply an 8% discount to products whose price exceeds $25. Here’s how you can do it:

• Create a new column for discounted prices (let’s say in cell D5).
• Enter the following formula in cell D5:

This formula calculates the discounted price for the product in cell C5 if its price exceeds $25.

• Press ENTER, and you’ll see the discounted price for “Gingerale” (assuming it exceeds $25).

• Use the Fill Handle to apply the formula to other cells in the column.

• The products in rows 7 and 8 won’t have a discount because their prices don’t exceed $25.

Example 2 – Discount for Products Below $40

Now, let’s set a discount for products with prices below $40:

• Create a new column for discounted prices (again, in cell D5).
• Insert this formula in cell D5:

This formula calculates the discounted price for the product in cell C5 if its price is below $40.

• Press ENTER, and you’ll see the discounted price for “Gingerale” (assuming its price is below $40).

• AutoFill the formula down to other cells.

The products in rows 6, 9, and 10 won’t have a discount because their prices aren’t below $40.

Example 3 – Discount for Products Within a Range ($15 to $50)

Suppose you want to set a discount for products priced between $15 and $50:

• Create a new column for discounted prices (once more, in cell D5).
• Enter this formula in cell D5:

The AND function ensures the condition falls within the price range.

• Press ENTER, and you’ll see the discounted price for “Gingerale” (assuming its price is between $15 and $50).

• AutoFill the formula down to other cells.

The products in rows 6, 7, and 9 won’t have a discount because their prices aren’t within the range of $15 to $50.

Example 4 – Calculating Discounts Based on Product Type

Suppose you want to apply discounts based on whether a product is of the “liquid” type. Follow these steps:

• Identify liquid products and mark them (e.g., with “(Liquid)”).
• Create a new column for discounted prices (let’s say in cell D5).
• In cell D5, insert the following formula:

• • This formula calculates the discounted price for the product in cell C5:
    • If the product is tagged as “liquid,” it applies the discount (1 – discount percentage) to the original price (C5).
    • If the product is not liquid, it keeps the original price (C5).

• Press ENTER to see the discounted price for “Gingerale” (assuming it’s a liquid product).

• Use the Fill Handle to apply the formula to other cells in the column.

The products in rows 6, 7, 9, and 10 won’t have a discount because they are not liquid-type products.

Practice Section

We have given you the dataset of this article so that you can practice these methods on your own.

Download Practice Workbook

You can download the practice workbook from here:

<< Go Back to Discount | Formula List | Learn Excel