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.
How to Use IF Function for Discounts in Excel: 4 Suitable Examples
In the dataset, we have prices of various products and also a conditional discount percentage.
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.
- Next, use the Fill Handle to AutoFill the lower cells.
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.
Read More: How to Calculate Multiple Discounts in Excel
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)
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.
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.
Read More: How to Calculate Discount Rate in Excel
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.
- 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.
Read More: How to Calculate 10 Percent Discount in Excel
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)
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.
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.
Read More: How to Calculate Discount Percentage with Formula in Excel
Practice Section
Here, we’re giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
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.
