Bonus Calculation

G

Guest

I have a spreadsheet where I would like to pay a bonus based on hours worked.

A B C D E F G H
1 Limits Bonus Rate Hours Amount
2 > 0 ≤ 20 0% $20 17 $
3 > 20 ≤ 40 5% $20 23 $
4 > 40 10% $20 23 $

A2 to D4 is the limits to be applied. The limits can be changed.
E2 to E4 is the percentage bonus. The percentage can be changed.
F2 to F4 is the hourly rate.
G2 to G4 are the hours worked

Require the spreadsheet to calculate the amount based on applied limits.

Many thanks for any assistance that you can provide.
 
B

Bob Phillips

If you change row 4 to
, 40,blank,blank,10%

you can use

=F2*G2*INDEX($E$2:$E$4,MATCH(G2,$B$2:$B$4))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
R

Roger Govier

Hi

B2:B4 needs to be 0, 20, 40
C2:D4 are superfluous
in H2
=G2*F2*(1+VLOOKUP(G2,$B$2:$E$4,4))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top