Hi, I am trying to make a nested IF/SUM formulas that increases in tiers and gets 100 times slower to increase each tier. I want the formula to react to the value of one other number (in this case cell J19). I am using Excel 2010.
This is my current formula but Excel is telling me "you've entered too many arguments for this function".
=IF(J19<500, SUM(J$9+(J19*0.2)), IF(J19<25500, SUM((J$9+(500*0.2)+((J19-500)*0.002)))), IF(J19<1275500, SUM((J$9+(500*0.2)+(25000*0.002)+((J19-25500)*0.00002)))), SUM((J$9+(500*0.2)+(25500*0.002)+(1249500*0.00002)+((J19-1275500)*0.0000002))))
If J9 is 0, I am hoping for a formula that results in:
If J19 is 250 then result is 50
If J19 is 500 then result is 100
If J19 is 1000 then result is 101
If J19 is 10000 then result is 119
If J19 is 25500 then result is 150
If J19 is 100500 then result is 151.50
If J19 is 1000500 then result is 169.50
If J19 is 1275500 then result is 175
---where I got up to before error message--
If J19 is ??? then results is 187.5
If J19 is ??? then results is 193.75
If J19 is ??? then results is 196.875
when it hits the tier it immediately starts slowing down by 100. Each tier occurs at half the added result as the previous tier (i.e. 100, 150, 175, 187.5, etc). I have already calculated the second and third tier are at 25500 and 127550 at this specific increase rate. There would be no sudden change in the number when it enters a new tier between, e.g. 1275499 and 2375500 will be very close to each other.
I tried the LN function but it isn't quite right for my intentions.
I'm not sure why I'm getting the error message. I'm hoping for five tiers of this formula.
This is my current formula but Excel is telling me "you've entered too many arguments for this function".
=IF(J19<500, SUM(J$9+(J19*0.2)), IF(J19<25500, SUM((J$9+(500*0.2)+((J19-500)*0.002)))), IF(J19<1275500, SUM((J$9+(500*0.2)+(25000*0.002)+((J19-25500)*0.00002)))), SUM((J$9+(500*0.2)+(25500*0.002)+(1249500*0.00002)+((J19-1275500)*0.0000002))))
If J9 is 0, I am hoping for a formula that results in:
If J19 is 250 then result is 50
If J19 is 500 then result is 100
If J19 is 1000 then result is 101
If J19 is 10000 then result is 119
If J19 is 25500 then result is 150
If J19 is 100500 then result is 151.50
If J19 is 1000500 then result is 169.50
If J19 is 1275500 then result is 175
---where I got up to before error message--
If J19 is ??? then results is 187.5
If J19 is ??? then results is 193.75
If J19 is ??? then results is 196.875
when it hits the tier it immediately starts slowing down by 100. Each tier occurs at half the added result as the previous tier (i.e. 100, 150, 175, 187.5, etc). I have already calculated the second and third tier are at 25500 and 127550 at this specific increase rate. There would be no sudden change in the number when it enters a new tier between, e.g. 1275499 and 2375500 will be very close to each other.
I tried the LN function but it isn't quite right for my intentions.
I'm not sure why I'm getting the error message. I'm hoping for five tiers of this formula.