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

 

[arthurbr]

Like this ? https://www.mcgimpsey.com/excel/variablerate1.html

 

[sflawson]

Like this ? https://www.mcgimpsey.com/excel/variablerate1.html

I'm blocked from the link

 

[arthurbr]

Try using http instead of https. The site might not have https

 

[Fluff]

How about

=IF(P14<=1000000,0.055*P14,IF(P14<=1500000,P14*0.065,IF(P14<=2000000,P14*0.075,"")))

 

[sflawson]

@Fluff it allows me to 1.4MM but once it crosses 1.5MM it doesn't work.

 

[Fluff]

In what way?

 

[sflawson]

@Fluff as soon as it crosses 2MM the formula reads FALSE. Up to 2MM it works. When it got to 2,000,001 that's when the FALSE response appears.

 

[kweaver]

Maybe you mean this?

=IF(P14<=1000000,0.055*P14,IF(P14<=1500000,P14*0.065,P14*0.075,""))

 

[Fluff]

When it got to 2,000,001 that's when the FALSE response appears.

In that case you did not use the formula I suggested.

+Fluff 1.xlsm
	O	P
14	5500	100,000
15	71500	1,100,000
16	120000	1,600,000
17		2,100,000
18
Master

Cell Formulas
Range	Formula
O14:O17	O14	=IF(P14<=1000000,0.055*P14,IF(P14<=1500000,P14*0.065,IF(P14<=2000000,P14*0.075,"")))