Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF SUM
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 |
#2
|
|||
|
|||
IF SUM
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 "TYE" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|