Solved

Excel: how to use if, and or statements to calculate bonus

Posted on 2014-03-31
6
290 Views
Last Modified: 2014-04-01
I need to calculate bonuses for employees based on their salary and number of children they have based on the following table                        
            
      
Please assist?
TABLE.png
0
Comment
Question by:badtz7229
6 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39968458
If you have salary in column A and Kids in column B, use this formula in column C:

=IF(AND(A2<800,B2>=3),300,IF(AND(A2<800,B2>0),150,IF(AND(A2<1000,B2>=3),200,IF(AND(A2<1000,B2>0),100,IF(AND(A2<1200,B2>=3),120,IF(AND(A2>=1200,B2>=3),60,IF(AND(A2>=1200,B2>0),50,0)))))))

Flyster
CalcBonus.xlsx
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39968483
Hi,

Or you could use

=MAX(IF(AND(A1<800,B1>=3),300),IF(A1<800,150),IF(AND(A1<1000,B1>=2),200),IF(A1<1000,100),IF(AND(A1<1200,B1>=3),120),IF(AND(A1>1200,B1>=3),60),50)

Open in new window

Regards
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39968692
If you were to reformat your table with Salary down the side, number of kids across the top and bonus in the grid, you could use an INDEX or MATCH lookup to find your required value.

Thanks
Rob H
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 39968718
By having a grid as suggested above I was able to get the attached results.

Question though, what happens for those with no kids? I have assumed zero bonus.

Thanks
Rob H
Bonus-chart.xlsx
0
 

Author Closing Comment

by:badtz7229
ID: 39969310
thank you . this worked successfully.
0
 

Author Comment

by:badtz7229
ID: 39969311
Flyster your solution worked but not entirely.
For the condition where :
salary kid
850            0
900            2
900            0

it returned 0. where in fact, it should've returned 100,200,100 respectively.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
My experience with Windows 10 over a one year period and suggestions for smooth operation
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.

830 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