?
Solved

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

Posted on 2014-03-31
6
Medium Priority
?
322 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 52

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

752 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