Afternoon all,

I'm running an incentive in work and I need some help with a particular formula. At the moment I work this out manually, I have attached below...

Each time an employee receives feedback they are rated out of 5 (5 being the highest) and the scores are totted up and an average score is given. What I want to work out is what the minimum amount of feedback an employee needs to receive to bump the average score up to the next level (At 0/5 intervals). A simple array rounds the numbers up to make it simple.

In the example the answer is 2 (John receives feedback of 5 twice and his average score will bump up to 4.5)

Feedback Scores - Jason
v C D E
Rating Number Score

c6 5 20 100
c7 4 5 20
c8 3 6 18
c9 2 2 4
c 10 1 1 1
c11
c12 Total 34 4.205882353 (=SUM(E6:E10)/D12)


Current Rating 4.0

Simple array to round up number

=VLOOKUP(E12,C22:E29,3,TRUE)

Lower Limit Upper Limit Average
1.25 1.74 1.5
1.75 2.24 2
2.25 2.74 2.5
2.75 3.24 3
3.25 3.74 3.5
3.75 4.24 4
4.25 4.74 4.5
4.75 5 5





HELP! Its frustrating the hell out of me!

Thanks in advance