Solved

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

Posted on 2014-03-31
6
273 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 48

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 31

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 31

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now