I don't get an error. Can you post the file (without confidential information)?

Solved

Posted on 2011-10-27

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(B4>1000,(B4*0.06)))

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

8 Comments

35.00

480.00

300.00

FALSE

First one it's fine which is 35 but for b3 480.00 is wrong

=IF(B2<1000,B2*0.05,IF(AND

Sincerely,

Ed

Here is what it came up.

35.00

800.00

300.00 >>>>>>>>>>>>>>>>> it should be 10% and it should be 500

60.00

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%

Title | # Comments | Views | Activity |
---|---|---|---|

excel formula or VBA | 7 | 36 | |

Macro: print each sheet to pdf using ExportAsFixedFormat | 11 | 27 | |

Excel -- need lookup or match function | 4 | 36 | |

excel - employee availability remaining after scheduled | 7 | 34 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**21** Experts available now in Live!