Formula to increase percentage in smooth ramp

[sflawson]

Hi All,
I had a wonderful formula that I lost and I'm trying to rebuild.

What I would like to do is, gradually increase a percentage applied to a total between multiple values.

For example, if the total was 1,000,000 I would like to calculate 5.5%, but as the total increased to 1,500,000, I would like the percentage to increase to 6.5%, then if the total increased to 2,000,000 or above I would like the percentage to increase to 7.5% in a smooth ramp. For now that's as far as I need to go but I'm sure one day I'll need to expand on this and continue adding other "breakpoints" for the increases.

I used the below formula that I found on a previous post but cannot get my 3rd piece to work.
=IFS(P14<=1000000,0.055*P14,AND(P14>1000000,P14<=1500000),P14*0.065)

This is where I'm failing (in red)...
=IFS(P14<=1000000,0.055*P14,AND(P14>1000000,P14<=1500000),P14*0.065),AND(P14>=1500000,P14<=2000000),P14*.075)

Thanks
Steph

 

[Dave Patton]

The description and the formula are not consistent. Considering the description, the following is possible.
Try any of the three suggestions.

Please review and compare to your expected results.

Commissions 2021.xlsm
	A	B	C	D	E	F	G
1
2	2,000,000	150,000.00	150,000.00	150,000.00		0.00	5.5%
3	500,000	27,500.00	27,500.00	27,500.00		1,499,999.99	6.5%
4	1,000,000	55,000.00	55,000.00	55,000.00		2,000,000.00	7.5%
5	1,100,000	60,500.00	60,500.00	60,500.00
6	1,600,000	104,000.00	104,000.00	104,000.00
7	2,500,000	187,500.00	187,500.00	187,500.00
8	10,000,000	750,000.00	750,000.00	750,000.00
9	10,000	550.00	550.00	550.00
1b

Cell Formulas
Range	Formula
B2:B9	B2	=A2*IF(A2<=1500000,0.055,IF(A2<=1999999.99,0.065,0.075))
C2:C9	C2	=A2*LOOKUP(A2,$F$2:$G$4)
D2:D9	D2	=A2*LOOKUP(A2,{0,0.055;1499999.99,0.065;2000000,0.075})

 

[Dave Patton]

Commissions 2021.xlsm
	A	B	C	D	E	F	G
1
2	10,000	550.00	550.00	550.00		0.00	5.5%
3	1,000,000	55,000.00	55,000.00	55,000.00		1,500,000.00	6.5%
4	1,500,000	97,500.00	97,500.00	97,500.00		2,000,000.00	7.5%
5	1,750,000	113,750.00	113,750.00	113,750.00
6	2,000,000	150,000.00	150,000.00	150,000.00
7	2,500,000	187,500.00	187,500.00	187,500.00
8	10,000,000	750,000.00	750,000.00	750,000.00
1b

Cell Formulas
Range	Formula
B2:B8	B2	=A2*IF(A2<1500000,0.055,IF(A2<2000000,0.065,0.075))
C2:C8	C2	=A2*LOOKUP(A2,$F$2:$G$4)
D2:D8	D2	=A2*LOOKUP(A2,{0,0.055;1500000,0.065;2000000,0.075})

 

[sflawson]

I'm sorry, I don't think I've explained what I'm looking for very well.

Manually typing out and calculating it myself it would look like this.

I'm needing everything up to 1.5MM to get calculated at 5.5%, 1.5M - 2M (500k) calculated at 6.5%, and anything above but not including 2M to get calculated at 7.5%.

267,079.00	14,689.35	5.50%​
270,362.00	14,869.91	5.50%​
278,250.00	15,303.75	5.50%​
287,148.00	15,793.14	5.50%​
319,564.00	17,576.02	5.50%​
277,908.00	17,288.05	5/5%/6.5%
299,690.00	19,479.71	6.5%/7.5%
305,493.00	22,911.98	7.50%​
295,848.00	22,188.60	7.50%​
301,503.00	22,612.73	7.50%​
304,252.00	22,818.90	7.50%​
303,898.00	22,792.35	7.50%​
3,510,995.00	228,324.48

 

[kweaver]

Aren't all of these values you show above (except the last) less than 1.5 million? So, they're all 5.5%?
That's clearly not the case in your illustration. What percent should be taken at 299,690? Why not 5.5% and you have 6.5%?
Are you comparing CUMULATIVE sums?

Book2
	A	B	C
1			Check
2	267,079.00	14,689.35	5.5%
3	270,362.00	14,869.91	5.5%
4	278,250.00	15,303.75	5.5%
5	287,148.00	15,793.14	5.5%
6	319,564.00	17,576.02	5.5%
7	277,908.00	18,064.02	6.5%
8	299,690.00	22,476.75	7.5%
9	305,493.00	22,911.98	7.5%
10	295,848.00	22,188.60	7.5%
11	301,503.00	22,612.73	7.5%
12	304,252.00	22,818.90	7.5%
13	303,898.00	22,792.35	7.5%
14	3,510,995.00	263,324.63	7.5%
Sheet4

Cell Formulas
Range	Formula
B2:B14	B2	=IF(SUM($A$2:A2)<1500000,5.5%,IF(SUM($A$2:A2)<=2000000,6.5%,7.5%))*A2
C2:C14	C2	=B2/A2

 

[sflawson]

Aren't all of these values you show above (except the last) less than 1.5 million? So, they're all 5.5%?
That's clearly not the case in your illustration. What percent should be taken at 299,690? Why not 5.5% and you have 6.5%?T

The value of all of the above number + 299,690 = 2,000,001. So, 299,689 is calculated at 6.5% and 1 is calculated at 7.5%.

Low Threshold	High Threshold
$0​	$1,500,000​	5.5%​
$1,500,001​	$2,000,000​	6.5%​
$2,000,001​	and over	7.5%​

 

[Dave Patton]

Consider the following
Try the version that you prefer.
It is not essential to use Named Ranges but it can yield a formula that is easier to read.

C2 The array of bracket data and the array of rate differentials are named. Use Name Manager and name
aB for Brackets ={0;1500000;2000000} and rate differentials aR ={0.055;0.01;0.01}

Commissions 2021.xlsm
	A	B	C	D
1	Total sales	3,510,995.00
2	Commission	228,324.63	228,324.63	228,324.63
3	Commission Arithmetic	228,324.63
4
5
6	Brackets [rB]	Rates	Rate Deltas [rR]
7
8	0	5.5%	5.50%	82,500.00
9	1,500,000	6.5%	1.00%	32,500.00
10	2,000,000	7.5%	1.00%	113,324.63
11	1E+308
12
2a

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
D2	D2	=SUMPRODUCT(--(B1>A8:A10),B1-A8:A10,B8:B10-B7:B9)
B2	B2	=SUMPRODUCT(--(B1>A8:A10),B1-A8:A10,B8:B10-B7:B9)
B3	B3	=SUM(D8:D10)
C8	C8	=B8-N(B6)
D8:D10	D8	=MAX(0,MIN($B$1,A9)-A8)*B8
C9:C10	C9	=B9-N(B8)
A11	A11	=BigNum

 

[Dave Patton]

Commissions 2021.xlsm
	A	B	C	D
1	Total sales	1,500,000.00
2	Commission	82,500.00	82,500.00	82,500.00
3	Commission Arithmetic	82,500.00
4
5
6	Brackets [rB]	Rates	Rate Deltas [rR]
7
8	0	5.5%	5.50%	82,500.00
9	1,500,000	6.5%	1.00%	0.00
10	2,000,000	7.5%	1.00%	0.00
11	1E+308
2a

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
D2	D2	=SUMPRODUCT(--(B1>A8:A10),B1-A8:A10,B8:B10-B7:B9)
B2	B2	=SUMPRODUCT(--(B1>A8:A10),B1-A8:A10,B8:B10-B7:B9)
B3	B3	=SUM(D8:D10)
C8	C8	=B8-N(B6)
D8:D10	D8	=MAX(0,MIN($B$1,A9)-A8)*B8
C9:C10	C9	=B9-N(B8)
A11	A11	=BigNum

 

[Dave Patton]

Please check your exact definition of the brackets.

Commissions 2021.xlsm
	A	B	C
14
15	267,079.00	14,689.35
16	270,362.00	29,559.26
17	278,250.00	44,863.01
18	287,148.00	60,656.15
19	319,564.00	78,232.17
20	277,908.00	95,520.22
21	299,690.00	115,000.08
22	305,493.00	137,912.05
23	295,848.00	160,100.65
24	301,503.00	182,713.38
25	304,252.00	205,532.28
26	303,898.00	228,324.63
27
28	3,510,995.00	228,324.63
2a

Cell Formulas
Range	Formula
B15:B26	B15	=SUMPRODUCT(--(SUM($A$15:A15)>aB),SUM($A$15:A15)-aB,aR)
B28	B28	=SUMPRODUCT(--(A28>aB),A28-aB,aR)

 

[Dave Patton]

The difference of 0.15 is in 19479.71
You stated The value of all of the above number + 299,690 = 2,000,001. So, 299,689 is calculated at 6.5% and 1 is calculated at 7.5%.

=299689*0.065+1*0.075-19479.71=0.15

 

[sflawson]

Consider the following
Try the version that you prefer.
It is not essential to use Named Ranges but it can yield a formula that is easier to read.

C2 The array of bracket data and the array of rate differentials are named. Use Name Manager and name
aB for Brackets ={0;1500000;2000000} and rate differentials aR ={0.055;0.01;0.01}

Commissions 2021.xlsm
	A	B	C	D
1	Total sales	3,510,995.00
2	Commission	228,324.63	228,324.63	228,324.63
3	Commission Arithmetic	228,324.63
4
5
6	Brackets [rB]	Rates	Rate Deltas [rR]
7
8	0	5.5%	5.50%	82,500.00
9	1,500,000	6.5%	1.00%	32,500.00
10	2,000,000	7.5%	1.00%	113,324.63
11	1E+308
12
2a

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
D2	D2	=SUMPRODUCT(--(B1>A8:A10),B1-A8:A10,B8:B10-B7:B9)
B2	B2	=SUMPRODUCT(--(B1>A8:A10),B1-A8:A10,B8:B10-B7:B9)
B3	B3	=SUM(D8:D10)
C8	C8	=B8-N(B6)
D8:D10	D8	=MAX(0,MIN($B$1,A9)-A8)*B8
C9:C10	C9	=B9-N(B8)
A11	A11	=BigNum

Thank you!!! This was very helpful and I can even play with the percentages and numbers.