asked on

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

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

Flyster

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

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

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

Rob Henson

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

tesmc

ASKER

thank you . this worked successfully.

tesmc

ASKER

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.