Formula

[Porlie]

How do I write a formula for the following salary bands please

For salaries between zero and 30,000 = salary x 95%

For salaries between 30,001 - 70,000 = 30,000 x 95% + balance x 92.5%

For salaries over 70,000 = 30,000 x 95% + 40,000 x 92.5% + balance x 90%

 

[Ms-Exl-Learner]

Can you please explain here what do you mean about the word balance?

Remember to Click Yes, if this post helps!

 

[David Biddulph]

=IF(A1<=30000,A1*95%,IF(A1<=70000,30000*95%+(A1-30000)*92.5%,30000*95%+40000*92.5%+(A1-70000)*90%))
or
=A1*95%-MAX(0,A1-30000)*2.5%-MAX(0,A1-70000)*2.5%

 

[Porlie]

Hi
The balance is the difference between the value and 30,001 for salaries
between 30,000 and 70,000 and for salaries over 70,001 for example 125,000
the balance is
125,000 - 70,000 = 55,000

 

[Ms-Exl-Learner]

Assume that you are having the Salary value in A1 cell and in B1 cell paste
this formula.

=IF(AND(A1>0,A1<=30000),A1*95/100,IF(AND(A1>30001,A1<=70000),(((30000*95/100)+(A1-30000))*92.5/100),IF(A1>70000,(((((30000*95/100)+40000)*92.5/100)+(A1-70000))*90/100),"")))

Change the cell reference A1 to your desired cell, if required.

Remember to Click Yes, if this post helps!

 

[David Biddulph]

No.
You've got your parentheses wrong.
You'll see that you get a lower result for 30002 than you had for 30000, and
a lower result for 70001 than for 70000.

Compare your results with my two suggestions:
=IF(A1<=30000,A1*95%,IF(A1<=70000,30000*95%+(A1-30000)*92.5%,30000*95%+40000*92.5%+(A1-70000)*90%))
or
=A1*95%-MAX(0,A1-30000)*2.5%-MAX(0,A1-70000)*2.5%

Also you've left a gap for values >30000 and <=30001. That could be implied
from what the OP asked, but I'm sure that wasn't what was intended.

 

[Porlie]

Many thanks, it worked a treat Jackie

Assume that you are having the Salary value in A1 cell and in B1 cell paste
this formula.

=IF(AND(A1>0,A1<=30000),A1*95/100,IF(AND(A1>30001,A1<=70000),(((30000*95/100)+(A1-30000))*92.5/100),IF(A1>70000,(((((30000*95/100)+40000)*92.5/100)+(A1-70000))*90/100),"")))

Change the cell reference A1 to your desired cell, if required.

Remember to Click Yes, if this post helps!

 

[David Biddulph]

I would recommend that you check the numbers from that formula, because that
doesn't seem to be what you asked for. See my earlier messages.

 

[Bernd P]

Hello,

Just for the fun of it:
=Interp({0;30000;70000;100000},{0;28500;65500;92500},A1)

My UDF Interp you can find here:
http://sulprobil.com/html/interpolate.html

Regards,
Bernd

 

[Ms-Exl-Learner]

Have a look in David sir formula and formula which I have provided. Since
both are getting different results. So check once again whether the formula
provided by me is correct.