We can easily calculate a single discount for a product. However, things get really complicated when we try to find product prices after more than one discount. In this article, we are going to show you 4 easy methods of how to calculate multiple discounts in Excel and use that to find discounted prices.
How to Calculate Multiple Discounts in Excel: 4 Easy Ways
To demonstrate our methods we have taken a dataset consisting of 4 columns: “Product”, “Listed Price”, “Discount 1”, and “Discount 2”. Additionally, we will change this dataset throughout our methods. The formula to find discounted price of a product is as follows –
Or, Discounted Price = Listed Price * (1-Discount Percentage)”
Here, the discount percentage will be simple for a single discount. However, for 2 or even 3 discounts, we will need to consider the effective discount rate. The way to calculate that will be described in detail in our methods.
1. Using a Generic Formula to Calculate Multiple Discounts in Excel
For the first method, we will use a generic formula to calculate double discounts in Excel. We have added two columns named “Effective Discount”, and “Revised Price” in the dataset. First, we will convert the two discounts into an equivalent discount rate, and using that rate we will calculate our Revised Price.
Steps:
- First, select the cell range F5:F10 and type the following formula.
=D5+E5-(D5*E5)
This formula merges our discount rates into a single discount rate by adding and subtracting the multiple values.
- Then, press CTRL+ENTER.
- So, this will AutoFill the formula to the rest of the cells.
- Next, we will calculate the Revised Price.
- Therefore, select the cell range G5:G10 and type this formula.
=C5*(1-F5)
This formula calculates the discounted price as per the discussion at the very beginning of this article.
- Lastly, press CTRL+ENTER.
Thus, we have shown you the first method of calculating multiple discounts in Excel.
2. Use of Multiplication Operator to Calculate Multiple Discounts in Excel
In this section, we are going to calculate triple discounts in Excel. Again, we have changed our dataset a little bit. Now, there are three discounts and we will use a formula to convert these discounts into a single equivalent discount. Lastly, we will multiply it by the Listed Price to get our Final Price.
Steps:
- To begin with, type the following formula in cell C15.
=(1-C12)*(1-C13)*(1-C14)
This formula finds the Price Multiplier for us. As our formula is in percentage format, we subtract 1 from it, where 1 equals 100%. Then, we multiply all the values to get the result. This formula will work for 4 or even more discounts, we just need to keep subtracting from 1, and multiplying it.
- Then, press ENTER.
We will get 88% as our output as per the discussion above.
- After that, select the cell range D5:D10 and type this formula.
=C5*$C$15
Remember to use the absolute cell reference in this formula.
This will return the discounted price for triple discounts in Excel.
- Lastly, press CTRL+ENTER.
In conclusion, this is the second method to calculate multiple discounts in Excel.
3. Calculate Multiple Discounts by Combining Nested IF & AND Functions
For the third method, we will use Nested IF & AND functions to calculate multiple discounts in Excel. We will get two discounts here – a flat discount and based on the number of units purchased.
Steps:
- To begin with, type the following formula in cell E5.
=D5*(1-$D$13)*IF(C5<=$C$14,(1-$D$14),IF(AND(C5>$C$14,C5<=$C$15),(1-$D$15),(1-$D$16)))
Formula Breakdown
- Here, (1-D13) represents the flat discount. This does not depend on the number of units purchased.
- Then, we have nested IF functions in this formula. Our condition is as follows –
- Firstly, for 100 or fewer units purchased will provide a 3% discount.
- Then, more than 100 but 200 or less will yield a 4% discount. For this, we have added the AND function inside the nested IF function.
- Lastly, above 200 units will get a 6% discount rate. If everything returns False, we will use this discount in the formula.
- Finally, we multiply the discount rate by the Listed Price to get the Discounted Price.
- Next, press ENTER.
We’ll get the Discounted Price here. Now using the Fill Handle, we will use this formula for the other cells.
Therefore, we have calculated multiple discounts in Excel and used that to find discounted prices.
4. Merging PRODUCT and IF Functions to Calculate Multiple Discounts
For the last method, we will combine the PRODUCT and IF functions to calculate multiple discounts in Excel. Again, we will get a discount based on the number of units purchased. If we buy 100 or more but less than 200 units we will get a 7% discount. However, if we buy less than 100 units, we will not get any discounts. Without further ado, let us jump into the steps.
Steps:
- First, select the cell range E5:E10 and type the following formula.
=D5*PRODUCT(IF(C5>=$C$13:$C$16,1-$D$13:$D$16,1))
Formula Breakdown
- First, we are using an array formula here. In Microsoft 365, we can simply press ENTER. However, for earlier versions, we will need to press CTRL+SHIFT+ENTER.
- Inside the IF function, we are checking whether our unit purchased is greater or equal.
- Then, we take the related discount rates. If 200 or more units are purchased but less than 300 then we will get two discounts: 7% and 9% and so on.
- In our case, our purchased unit is 100, hence we will get a 7% discount.
- For 650 units purchased in cell C8, we will get 4 discounts.
- Then, for less than 100 units purchased, we will get no discount.
- Finally, we are multiplying the discount rate by the Listed Price to get the Discounted Price.
- Lastly, press ENTER.
This will AutoFill the formula. Thus, we have calculated multiple discounts in Excel and used that to find reduced single unit prices.
Practice Section
We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.
Download Practice Workbook
Conclusion
We have shown you 4 quick methods of how to calculate multiple discounts in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Thanks for reading, keep excelling!
<< Go Back to Discount | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
