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

[TreworBG]

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!

 

[severynm]

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
	A	B	C	D
1	Product	Quantity	Price	Total
2	a	19	$ 8.05	$ 152.95
3	b	29	$ 12.11	$ 351.19
4	c	10	$ 15.50	$ 155.00
5	d	35	$ 7.85	$ 274.75
6	e		$ 7.85	$ -
7	f		$ 10.00	$ -
8	g		$ 14.90	$ -
9	Total			$ 933.89
10
11			Discount	25%
12
13			Total	$ 700.42
Sheet2

Cell Formulas
Range	Formula
D2:D8	D2	=[@Quantity]*[@Price]
D9	D9	=SUBTOTAL(109,[Total])
D11	D11	=IFS(Table2[[#Totals],[Total]]<500,0,Table2[[#Totals],[Total]]<1000,0.25,Table2[[#Totals],[Total]]<1500,0.5,Table2[[#Totals],[Total]]>=1500,0.35)
D13	D13	=Table2[[#Totals],[Total]]*(1-D11)