perla1962
asked on
IF FUNCTION EXCEL 2010
Hi,
I’m preparing a spreadsheet for people who work with us. They asked me to give bonus to the people who work part time and full time.
For people who makes less than 1000.00 give them 5%
For the people who makes more than 1000.00 to give them 6%
For the people who makes more than 5000.00 10%
Employee Payment
5601 700
3407 8,000
1000 5,000
6688 1,000
I came up with this formula but I get an error message can you please help me.
=IF(B4<1000,(B4*0.05),IF(B
I’m preparing a spreadsheet for people who work with us. They asked me to give bonus to the people who work part time and full time.
For people who makes less than 1000.00 give them 5%
For the people who makes more than 1000.00 to give them 6%
For the people who makes more than 5000.00 10%
Employee Payment
5601 700
3407 8,000
1000 5,000
6688 1,000
I came up with this formula but I get an error message can you please help me.
=IF(B4<1000,(B4*0.05),IF(B
etech0
I don't get an error. Can you post the file (without confidential information)?
ASKER
Here is the error
35.00
480.00
300.00
FALSE
First one it's fine which is 35 but for b3 480.00 is wrong
35.00
480.00
300.00
FALSE
First one it's fine which is 35 but for b3 480.00 is wrong
Try this:
=IF(B2<1000,(B2*0.05),IF(B
=IF(B2<1000,(B2*0.05),IF(B
You may use this formula as well:
=IF(B2<1000,B2*0.05,IF(AND
Sincerely,
Ed
=IF(B2<1000,B2*0.05,IF(AND
Sincerely,
Ed
ASKER
It came up fine but 5000 comes at 6% instead of 10
Here is what it came up.
35.00
800.00
300.00 >>>>>>>>>>>>>>>>> it should be 10% and it should be 500
60.00
Here is what it came up.
35.00
800.00
300.00 >>>>>>>>>>>>>>>>> it should be 10% and it should be 500
60.00
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think one thing that hasn't been pointed out yet is your logic error in the first post and why you were seeing those results. Understanding that will help you know where you need to go.
Your formula, broken out a different way, would look like this in pseduo-code:
if (B4 < 1000) then
bonus = B4 * 5%
else if (B4 > 1000) then
bonus = B4 * 6%
endif
This code works correctly for all amounts below 1000. It also works for amounts from 1000 to 5000 (your stated ceiling for the 6% bracket, but not reflected in your original formula). Therefore, when you supply 8000 to the formula, you will still get 8000 * 6% because you have not provided logic for those amounts for 5000 and above. As a couple of the other posters have shown in formulas, to get 10% for those 5000+ amounts, you need to add that logic.
Now, look carefully at your code. You didn't ask about the "FALSE" result for the last line of your sample data (1000), but I want to make sure you understand what is happening here. Given the logic, you've not accounted for what happens for this value. Should it be 5% or 6%. You would need a B4 <= 1000 in the IF for the prior case and B4 >= 1000 in the ELSE IF for the later.
The reason you are getting 300 for 5000 is that I suspect you are using the formula provided by etech0. His check only applies 10% to those values ABOVE 5000 (those that make 5000 will still receive 6%). This is consistent with your language in the original post, but based on your last post, I am assuming you want 10% for 5000 and above. In that case, MIDSUPERB's formula will provide the following result:
< 1000 = 5%
1000 to 4999 = 6%
5000+ = 10%
Your formula, broken out a different way, would look like this in pseduo-code:
if (B4 < 1000) then
bonus = B4 * 5%
else if (B4 > 1000) then
bonus = B4 * 6%
endif
This code works correctly for all amounts below 1000. It also works for amounts from 1000 to 5000 (your stated ceiling for the 6% bracket, but not reflected in your original formula). Therefore, when you supply 8000 to the formula, you will still get 8000 * 6% because you have not provided logic for those amounts for 5000 and above. As a couple of the other posters have shown in formulas, to get 10% for those 5000+ amounts, you need to add that logic.
Now, look carefully at your code. You didn't ask about the "FALSE" result for the last line of your sample data (1000), but I want to make sure you understand what is happening here. Given the logic, you've not accounted for what happens for this value. Should it be 5% or 6%. You would need a B4 <= 1000 in the IF for the prior case and B4 >= 1000 in the ELSE IF for the later.
The reason you are getting 300 for 5000 is that I suspect you are using the formula provided by etech0. His check only applies 10% to those values ABOVE 5000 (those that make 5000 will still receive 6%). This is consistent with your language in the original post, but based on your last post, I am assuming you want 10% for 5000 and above. In that case, MIDSUPERB's formula will provide the following result:
< 1000 = 5%
1000 to 4999 = 6%
5000+ = 10%
ASKER
=IF(B2<1000,B2*0.05,IF(AND
EXELLENTE THANK YOU,
EXELLENTE THANK YOU,