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

We’ll use a dataset consisting of 4 columns: Product, Listed Price, Discount 1, and Discount 2. We will change this dataset throughout our methods. The formula to find a discounted price of a product is as follows:

“Discounted Price = Listed Price – Listed Price * Discount Percentage
OR Discounted Price = Listed Price * (1-Discount Percentage)”

The discount percentage will be simple for a single discount. However, for multiple discounts, we will need to consider the effective discount rate. The way to calculate that will be described in detail in our methods.

Method 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:

Select the cell range F5:F10 and insert 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.

Press CTRL + ENTER. This will AutoFill the formula to the rest of the cells.
Select the cell range G5:G10 and insert this formula.

=C5*(1-F5)

This formula calculates the discounted price.

Press CTRL + ENTER.

Method 2 – Use the Multiplication Operator to Calculate Multiple Discounts in Excel

We are going to calculate triple discounts in Excel. We have changed our dataset a little bit. There are three discounts and we will use a formula to convert these discounts into a single equivalent discount.

Steps:

Use the following formula in cell C15.

=(1-C12)*(1-C13)*(1-C14)

This formula finds the Price Multiplier. 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, just keep subtracting from 1 and multiplying it.

Press ENTER.

We will get 88% as our output.

Select the cell range D5:D10 and use this formula.

=C5*$C$15

Use the absolute cell reference for the multiplier.

Press CTRL + ENTER.

Method 3 – Calculate Multiple Discounts by Combining Nested IF and AND Functions

We will apply two discounts: a flat discount and one based on the number of units purchased.

Steps:

Use 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

(1-D13) represents the flat discount. This does not depend on the number of units purchased.
We have nested IF functions in this formula. Our condition is as follows –
- For 100 or fewer units purchased will provide a 3% discount.
- For between 100 and 200, it will yield a 4% discount. We have added the AND function inside the nested IF function.
- Above 200 units will get a 6% discount rate. If everything returns False, we use this discount in the formula.
We multiply the discount rate by the Listed Price to get the Discounted Price.

Press ENTER.
Using the Fill Handle, apply this formula for the other cells.

Here are the results.

Method 4 – Merging PRODUCT and IF Functions to Calculate Multiple Discounts

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.

Steps:

Select the cell range E5:E10 and insert the following formula.

=D5*PRODUCT(IF(C5>=$C$13:$C$16,1-$D$13:$D$16,1))

Formula Breakdown

We are using an array formula here. In Microsoft 365, apply it with ENTER. However, for earlier versions, press CTRL + SHIFT + ENTER.
Inside the IF function, we are checking whether the purchased units go over a particular benchmark.
We used the related discount rates to use as multipliers.
We are multiplying the discount rate by the Listed Price to get the Discounted Price.