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.

- For
- 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**.

- Press
**ENTER**.

This will **AutoFill **the formula.

## Practice Section

We have added a practice dataset for each method in the **Excel** file. You can use it to test the methods.

**Download the Practice Workbook**

**<< Go Back to Discount | Formula List | Learn Excel**