Formula
 

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%

Formula
 

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

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%

Formula
 

=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%
--
David Biddulph

"Porlie" wrote in message
...
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%

Formula
 

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" wrote:

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

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%

Formula
 

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

=IF(AND(A10,A1<=30000),A1*95/100,IF(AND(A130001,A1<=70000),(((30000*95/100)+(A1-30000))*92.5/100),IF(A170000,(((((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!

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

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" wrote:

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

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%

Formula
 

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.
--
David Biddulph

"Ms-Exl-Learner" wrote in message
...
Assume that you are having the Salary value in A1 cell and in B1 cell
paste
this formula.

=IF(AND(A10,A1<=30000),A1*95/100,IF(AND(A130001,A1<=70000),(((30000*95/100)+(A1-30000))*92.5/100),IF(A170000,(((((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!

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

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" wrote:

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

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%

Formula
 

Many thanks, it worked a treat Jackie

"Ms-Exl-Learner" wrote:

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

=IF(AND(A10,A1<=30000),A1*95/100,IF(AND(A130001,A1<=70000),(((30000*95/100)+(A1-30000))*92.5/100),IF(A170000,(((((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!

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

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" wrote:

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

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%

Formula
 

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.
--
David Biddulph

"Porlie" wrote in message
...
Many thanks, it worked a treat Jackie

"Ms-Exl-Learner" wrote:

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

=IF(AND(A10,A1<=30000),A1*95/100,IF(AND(A130001,A1<=70000),(((30000*95/100)+(A1-30000))*92.5/100),IF(A170000,(((((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!

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

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" wrote:

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

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%

Formula
 

Hello,

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

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

Regards,
Bernd

Formula
 

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.

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

Many thanks, it worked a treat Jackie

"Ms-Exl-Learner" wrote:

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

=IF(AND(A10,A1<=30000),A1*95/100,IF(AND(A130001,A1<=70000),(((30000*95/100)+(A1-30000))*92.5/100),IF(A170000,(((((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!

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

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" wrote:

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Porlie" wrote:

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%