Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-03-31
6
Medium Priority
?
330 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 53

Accepted Solution

by:
Rgonzo1971 earned 2000 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

650 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