Help with IF

mag47

New Member
Joined
Oct 21, 2017
Messages
2
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
what version of excel are you using
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top