?
Solved

IF FUNCTION EXCEL 2010

Posted on 2011-10-27
8
Medium Priority
?
234 Views
Last Modified: 2012-05-12
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
Comment
Question by:perla1962
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 10

Expert Comment

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

Author Comment

by:perla1962
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

by:etech0
ID: 37042723
Try this:

=IF(B2<1000,(B2*0.05),IF(B2>5000,(B2*0.1),B2*0.06))
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:MINDSUPERB
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

by:perla1962
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

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

Sincerely,
Ed
0
 
LVL 4

Expert Comment

by:AgeOfEmpires
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

by:perla1962
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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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.

840 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question