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

The article will show you 3 genuine examples of how to use IF Function in Excel for discounts sales. If you are a businessman, you may offer customers discounts on various products so that they get convinced to buy those products. You can set the discount on different conditions such as if the price of a group of products exceeds a definite amount, those products can be sold to the customers with a discount. There are plenty of other ways to set a condition for discount. We’ll show you 4 of them in this article.


Download Practice Workbook


4 Examples to Use IF Function for Discounts in Excel

In the dataset, we have prices of various products and also a conditional discount percentage.

how to use if function in excel for discounts intro


1. Applying Excel IF Function When Discount Is Applicable for a Greater Price

Suppose, if the products in the dataset have a price that exceeds 25 bucks, the owner of that shop offers an 8% discount on them. We are going to describe the process that he should follow.

Steps:

  • First, make a column for discounted price and type the following formula in cell D5.

=IF(C5>$C$12,C5*(1-$C$13),C5)

Here, the formula will show you the discounted price of the product in C5 if it meets the desired condition.

  • After that, press the ENTER button and you will see the discounted price for Gingerale as its price exceeds 25 bucks.

how to use if function in excel for discounts example 1

Finally, you will get the discount prices for the products with the help of the Excel IF Function.

If you look at the results, you can find that the products in the 7th and 8th rows don’t have a discount because their prices do not exceed 25 bucks.


2. When Discount Is Applicable for a Price Lower Than Fixed Rate

Now, we will show you how to set a discount if the price of the products is lower than a fixed rate. Suppose, if the price of products is less than 40 bucks, we will set a discount for them. Let’s see the process below for better understanding.

Steps:

  • First, make a column for discounted price and type the following formula in cell D5.

=IF(C5<$C$12,C5*(1-$C$13),C5)

how to use if function in excel for discounts example 2

Here, the formula will show you the discounted price of the product in C5 if it meets the desired condition.

  • After that, press the ENTER button and you will see the discounted price for Gingerale as its price is below 40 bucks.

  • Next, use the Fill Handle to AutoFill the lower cells.

how to use if function in excel for discounts example 2

Finally, you will get the discounted prices for the products with the help of the Excel IF Function.

If you look at the results, you can find that the products in the 6th, 9th, and 10th rows don’t have a discount because their prices are not below 40 bucks.


3. Using Excel IF Function When Discount Is Applicable for a Price within a Range

In this section, we will show you how to set a discount if the price of the products is within a range. Suppose, if the price of products is between 15 and 50 bucks, we will set a discount for them. Let’s see the process below for better understanding.

Steps:

  • First, make a column for discounted price and type the following formula in cell D5.

=IF(AND(C5>15,C5<50),C5*(1-$C$13),C5)

Here, the formula will show you the discounted price of the product in C5 if it meets the desired condition. The AND Function here helps to make the condition with the price range.

  • After that, press the ENTER button and you will see the discounted price for Gingerale as its price is between 15 and 50 bucks.

how to use if function in excel for discounts example 3

  • Next, use the Fill Handle to AutoFill the lower cells.

Finally, you will get the discount prices for the products that have a price range of 15 bucks to 50 bucks with the help of the Excel IF Function.

If you look at the results, you can find that the products in the 6th, 7th, and 9th rows don’t have a discount because their prices are not in the range of 15 to 50 bucks.


4. Calculating Discount Based on Product Type

If you want to set a discount based on the type of a product, you need to follow the instructions below.

Steps:

  • Suppose, you want to set a discount for liquid products. Tag that product with (Liquid mark), make a column for the discounted price and type the following formula in cell D5.

=IF(RIGHT(B5,8)="(Liquid)",C5*(1-$C$13),C5)

how to use if function in excel for discounts example 4

Here, the formula will show you the discounted price of the product in C5 if it meets the desired condition. The RIGHT Function here helps to make the condition with the character ‘(Liquid)‘. If the product is not liquid, there will be no discount.

  • After that, press the ENTER button and you will see the discounted price for Gingerale because it’s a liquid

  • Next, use the Fill Handle to AutoFill the lower cells.

how to use if function in excel for discounts

Finally, using Excel If Function, you will get the discount prices for the products based on their type.

If you look at the results, you can find that the products in the 6th, 7th, 9th, and 10th rows don’t have a discount because they are not liquid type products.


Practice Section

Here, we’re giving you the dataset of this article so that you can practice these methods on your own.


Conclusion

Suffice to say, you will learn how to use IF Function effectively in Excel for discounts after reading this article. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo