| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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% |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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% |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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% |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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% |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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% |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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% |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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% |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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% |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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% |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
