Showing the % discount related to the order sum which is based on that discount

TreworBG

New Member
Joined
Nov 12, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi All!

I am creating the Order Form and have a problem with applying the % discount. I checked the forum but the solutions found are based on the ready map - which does not apply (I guess) to my case.

So... First of all I use the "automatic" Excel Table. In $D$2 I want to have one of the following discounts:
0% if the order is < than $500
25% if the order is >=$500 and <$1000
30% if the order is >=$1000 and <$1500
35% if the order is >$1500

Column B is the product name
Column H is the ordered amount
Column I is the retail NET price
Column J is the retail GROSS price
Column K is the discounted price (retail NET price * $D$2)
Column M is the total net price after discount

Somewhere in the bottom (cell M119) is the auto sum of column M.

Now, the discount to be shown in $D$2 depends on the total order value (M119) BUT it needs to include the discount. In other words: my customer fills in the ordered amount in column H. I want to know when his order value with the discounted prices (e.g. 25%) exceeds $500 but won't reach $1000. And in the same time I want to show him the discount in $D$2.

One more example:
First customer chose items for total value of $489 - no discount apply, in $D$2 shows "0%" and total value in M119 is $489
Second customer filled the amounts and reached $700 - this value includes items already discounted (25%)

I hope it's clear somehow, I'll be really grateful for any advise,

Thanks!
1636758716309.png
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

severynm

Active Member
Joined
Jan 8, 2021
Messages
335
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum! :)

I mocked up a small example table below. To calculate your discount, look at using the IFS function (formula in D11 below). You can then apply this directly against the subtotal from the table to get the result after discount (formula in D13 below)

MrExcel.xlsm
ABCD
1ProductQuantityPriceTotal
2a19$ 8.05$ 152.95
3b29$ 12.11$ 351.19
4c10$ 15.50$ 155.00
5d35$ 7.85$ 274.75
6e$ 7.85$ -
7f$ 10.00$ -
8g$ 14.90$ -
9Total$ 933.89
10
11Discount25%
12
13Total$ 700.42
Sheet2
Cell Formulas
RangeFormula
D2:D8D2=[@Quantity]*[@Price]
D9D9=SUBTOTAL(109,[Total])
D11D11=IFS(Table2[[#Totals],[Total]]<500,0,Table2[[#Totals],[Total]]<1000,0.25,Table2[[#Totals],[Total]]<1500,0.5,Table2[[#Totals],[Total]]>=1500,0.35)
D13D13=Table2[[#Totals],[Total]]*(1-D11)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,560
Messages
5,838,114
Members
430,529
Latest member
PaperBoi5870

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top