Sliding Scale for Percentages

[sharkman1912]

So I am in a predicament. We have orders that range from $500 to over $500,000 and we charge a certain amount for an extra condition and we want to base that cost off a percentage of the order. The smaller orders we charge 2% and the max we will charge will be 0.5% at $500,000. Anything in between needs to be on a sort of sliding scale: as the price goes up the percentage should go down as well based off an equal scale from $500 to $500,000. Any help would be greatly appreciated.

 

[Dave Patton]

You did not provide information on the sliding scale or the rate increments.

Excel 2010
	A	B	C	D	E
1	200000	1.5%		500	2.0%
2		1.5%		160000	1.5%
3				320000	1.0%
4				500000	0.5%
5
3b

Cell Formulas
Range	Formula
B1	=LOOKUP(A1,D1:E4)
B2	=LOOKUP(A1,{500,0.02;160000,0.015;320000,0.01;500000,0.005})
E2	=E1-0.005
E3	=E2-0.005
E4	=E3-0.005

 

[sharkman1912]

Sorry about that! It is hard to convey what is going on in my head sometimes. Basically, I was wanting the percentage to decrease an equal amount every dollar the price goes up from $500 to $500,000. I know the number of change will be small, but that is kind of what we were wanting. Does that explain it better?

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	500000	0.005
2	0	0.02			500000	0.5000%
3	500000	0.015	0.000003%		1000	2.00%
4
3b

Cell Formulas
Range	Formula
A3	=A1-A2
B3	=B2-B1
C3	=B3/A3
F2	=0.02-E2*$C$3
F3	=ROUND(0.02-E3*$C$3,2)

Round as required
Look at Min and Max functions if required.

 

[sharkman1912]

Thanks Dave! I think this is very close to what I am needed, but can you help explain what the inputs are? I think B1 and 2 are the high and low end of the percentage spectrum, A1 and 2 and the high and low end of the price spectrum, but what about E2 and E3? And is the final percent F2?

 

[Dave Patton]

"very close to what I am needed" means ????

at E2 the rate is F2
at E3 the rate is F3

 

[sharkman1912]

It means I apparently don't proofread before sending a post . This is exactly what I was needing, just need to figure out how to get the price to stop going down once it gets past around 360,000................

 

[Dave Patton]

Excel 2010
	E	F
1	Gross	Discount
2	500,000.00	2,500.00
3	450,000.00	3,150.00
4	400,000.00	3,200.00
5	350,000.00	3,500.00
6	300,000.00	3,300.00
7	250,000.00	3,250.00
8	200,000.00	2,800.00
9	150,000.00	2,400.00
10	100,000.00	1,700.00
11	130,000.00	2,080.00
12	1,000,000.00	5,000.00
13
3b

Cell Formulas
Range	Formula
F2	=MAX(0.005,ROUND(0.02-E2*$C$3,3))*E2

Adjust the rounding per your requirements.