Excel Help - Should be simple for some.

MrKeeno

MrKeeno

MrKeeno

Associate
Joined
13 Mar 2004
Posts
1,929
Location
Derry
Hope you can help with this as my excelskills are severely lacking :)

We are introducing a bonus scheme in work for a bit of fun, hopefully to get a bit of healthy competition going between various teams. We had an old bonus system before my time and it is this they are looking to go with again. I want to use excel to calculate the bonus/points.

The target bonus needs to be easily changed, but say for instance it needs to be 96.8%

If a team gets:

Between target and +0.99% they get 4 points
Between +1% and +1.99% they get 6 points
Between +2% and +2.99% they get 9 points
Between +3% and +3.49% they get 14 points
+3.5% and above they get 16 points

Hopefully someone can help with this.

Thanks in advance.
 
VLOOKUP should cover this I reckon. You put that information into a column, using only the upper number I think so 0.99%, 1.99% etc and then in the column next to it the points for getting into that range. You'd then use VLOOKUP to lookup the percentage value in the table. When it finds it (you can set it to nearest IIRC and not just exact value), you tell VLOOKUP to look in the next column and it returns the points.
 
Is there any chance you could write a line of this for me and then I can duplicate it for the other targets? I honestly have no clue how to do it :D
 
A quick splash here....

If you make
Column A your target %
Column B your actual %
Column C your differnce (B-A)
Column D =INT(C1) (remove the decimal points)
Column E =VLOOKUP(D2,$J$1:$K$6,2,FALSE)

where J1-J6 is 0/1/2/3/4/5/6
where K1-K6 is 0/4/6/9/14/16

that'll work for starters and you should be able to work off that.
 
You must log in or register to reply here.
Back
Top Bottom