Solved

# IF FUNCTION EXCEL 2010

Posted on 2011-10-27
Medium Priority
234 Views
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)))
0
Question by:perla1962
• 3
• 2
• 2
• +1

LVL 10

Expert Comment

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

Author Comment

ID: 37042716
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
0

LVL 10

Expert Comment

ID: 37042723
Try this:

=IF(B2<1000,(B2*0.05),IF(B2>5000,(B2*0.1),B2*0.06))
0

LVL 19

Expert Comment

ID: 37042792
You may use this formula as well:

=IF(B2<1000,B2*0.05,IF(AND(B2>=1000,B2<5000),B2*0.06,B2*0.1))

Sincerely,
Ed
0

Author Comment

ID: 37042813
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
0

LVL 19

Accepted Solution

MINDSUPERB earned 2000 total points
ID: 37042828
Have you tried my suggestion at ID: 37042792?

Sincerely,
Ed
0

LVL 4

Expert Comment

ID: 37042842
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%
0

Author Closing Comment

ID: 37042844
=IF(B2<1000,B2*0.05,IF(AND(B2>=1000,B2<5000),B2*0.06,B2*0.1))

EXELLENTE THANK YOU,
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month14 days, 9 hours left to enroll