percentages with if/then help

[slitinx]

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

 

[acw]

percent

Hi

Assuming that the amount is in A2 then

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

Tony

 

[slitinx]

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

 

[tactps]

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.