IF SUM

[Guest]

Hi need need a foulmles for the following,

Total Sales Total Hrs Man Hours Prod
Bonus SPH

Paul Smith 4 7.25 8
91% £16 0.55

Total Sales Total Hrs Man Hours Prod
Bonus SPH

Paul Smith 4 7 8
88% £8 0.55

THIS IS A TABLE WHERE IT GET IN INFORT
Ratio Productivity Bonus
0.35 - 0.4 90% 2.00
0.41 - 0.5 90% 3.00
..51 – 0.6 90% 4.00
..61 – plus 90% 5.00

BELOW 90%

RATIO Bonus
0.35 - 0.4 1.00
0.41 - 0.5 1.50
..51 – 0.6 2.00
..61 – plus 2.50


CAN YOU HELP ON THIS I NEED A FORMULES TO WORK OUT THE BONUS

 

[Guest]

If you provide a minor change to your tables, then an IF and VLOOKUP can be
combined to calculate the bonus.
Your table would be three columns: Ratio, LowBonus, HighBonus. The entries
would be something like 0,0,0 in the first row, 0.35,1,2 in the second row,
etc. Suppose this table is in Sheet2!A1:C6.
If Paul's productivity and SPH are in E2 and G2, respectively, the bonus
calculation is something like
=if(e2>=90%,vlookup(g2,Sheet2!$A$1:$C$6,3),vlookup(g2,Sheet2!$A$1:$C$6,2)).
(In words, in the productivity is at least 90%, lookup up the 'high bonus'
associated with his SPH; otherwise, look up the 'low bonus'.).
--Bruce