How to Calculate Multiple Discounts in Excel (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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 –

Discounted Price = Listed Price – Listed  Price * Discount Percentage
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.

how to calculate multiple discounts in excel Intro


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)

how to calculate multiple discounts in excel Effective discounts

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.

how to calculate multiple discounts in excel generic formula


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)

how to calculate multiple discounts in excel Triple discounts

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.

how to calculate multiple discounts in excel

  • 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.

how to calculate multiple discounts in excel Nested IF

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.

how to calculate multiple discounts in excel Nested IF 2

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.

how to calculate multiple discounts in excel PRODUCT

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.

how to calculate multiple discounts in excel Final Step


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo