percentages with if/then help

slitinx

New Member
I have to pay royalties calculated thru 15 day periods. The royaltie total is calculated this way: the amounts are totalled then the first \$5000 I pay 50% then everything brought in after that is calculated at 20%. How do i make excel figure out my royalty amount from my summed amounts. I tried it this way:

if total > 5000 then multiply it by .25 if not then multiply it by .5 This
is fine after \$5000 but say my very first entry is \$6000, i need it to calculate the first \$5000 at 50% and the remaining \$1000 at 25% and add them together to give me the total, but at the same time once the first \$5000 is reached I only want it to calculate everything from then on at 25%. So every sheet from then on gets calculated at 25%. So confusing

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
percent

Hi

Assuming that the amount is in A2 then

=IF(A2>5000,2500+(A2-5000)*0.25,A2*0.5)

Tony

Close

Thank you for that formula. It works excellent for the first term. these balance are carried forward as terms so the total for each term is paid. That formula worked great for the first term but once the 5000 is reached the next term needs to be only calculated at 25%. If i paste that formula into the next term it computes 5000 of the total each time at 50% but the \$5000 was already reached in the first term so once reached i only wante 25%. So really the formula you wrote was right on the money cept for every term after that . Problem is that the first \$5000 might not get reached for a few terms

It sounds lie you need an accumulation.

Try something like:

=IF(sum(\$A\$2:\$A3)>5000,2500+(sum(\$A\$2:\$a3)-5000)*0.25-B2,sum(\$A\$2:\$a3)*0.5-B2)

asuuming that B2 contains the prior formula.

Replies
3
Views
956
Replies
30
Views
2K
Replies
19
Views
2K
Replies
10
Views
475
Replies
12
Views
568

Forum statistics

1,203,490
Messages
6,055,726
Members
444,814
Latest member
AutomateDifficulty

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.

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

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