Solved

# IF FUNCTION EXCEL 2010

Posted on 2011-10-27
166 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

LVL 10

Expert Comment

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

Author Comment

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

Try this:

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

LVL 19

Expert Comment

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

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

Have you tried my suggestion at ID: 37042792?

Sincerely,
Ed
0

LVL 4

Expert Comment

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

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

EXELLENTE THANK YOU,
0

## Featured Post

### Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.