Future number precdiction

[Fordy_deluxe]

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

[oeldere]

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

[MrShorty]

You indicate that "2" is the correct answer. Is that the only correct answer, or are there other possible solutions? What does this solution mean (it looks to me that it means "increase D6 by 2 to 22".

It looks to me like a basic algebra problem (with potentially multiple possible solutions).

E12=sum(e6:e10)/d12>=4.25
e6:e10=product(c6:d6)
It would appear that one of the values in column D is our "unknown" variable x.
It appears to me that some simple algebra should allow us to solve for D6 or D7 or D8 or D9 or D10. If I assume that D6 is the correct "unkown"

E12=[sum(e7:e10)+D6*C6]/[sum(d7:d10)+D6]>=4.25

If you need a refresher on solving these kind of algebra problems, I will refer you to this page http://purplemath.com/modules/solvelin3.htm

If you don't want to do the algebra, there are the "Goal Seek" and "Solver" utilities that could be invoked to find a solution to the problem (set target cell E12 to a value of 4.25 by changing d6).

[Fordy_deluxe]

Iv attached a crude version of the spread sheet im tryign to make work, i tried the forumla in the example above but cant seem to get it to work. The cell id liek the answer to display in is cell D17. In this example the correct answer is 2. As stated above the variable is D6.

Thanks in advance

[MrShorty]

It still likes an algebra problem first. Were you able to solve the algebra problem, or do we need to review the strategies for solving these kind of algebra problems? It still seems like it should be as simple as
1) solving the equation for D6. This will almost certainly be done outside of Excel.
2) put that equation into D17
3) and subtract the current value in D6 from that result.

[shg]

One way:

Row\Col	B	C	D	E	F	G	H	I	J	K	L
2	Rating	Count		Goal\Rating	1	2	3	4	5		B9: =SUMPRODUCT(B3:B7, C3:C7)/SUM(C3:C7)
3	5	20		1.25	402.0	(134.0)	(57.4)	(36.5)	(26.8)		F3 and across and down: =$C$9 * ($E3 - $B$9) / (F$2 - $E3)
4	4	5		1.75	111.3	(334.0)	(66.8)	(37.1)	(25.7)
5	3	6		2.25	53.2	266.0	(88.7)	(38.0)	(24.2)
6	2	2		2.75	28.3	66.0	(198.0)	(39.6)	(22.0)
7	1	1		3.25	14.4	26.0	130.0	(43.3)	(18.6)
8	Avg	Total		3.75	5.6	8.9	20.7	(62.0)	(12.4)
9	4.21	34		4.25	(0.5)	(0.7)	(1.2)	(6.0)	2.0
10				4.75	(4.9)	(6.7)	(10.6)	(24.7)	74.0

So, for example,

F3: If John got 402 1 ratings, his average would be 1.25

J6: If John lost 22 of his 5 ratings, his average would be 2.75

J9: John needs 2 more 5 ratings to average 4.25

J10: John needs 74 more 5 ratings to average 4.75

[Fordy_deluxe]

I still cant seem to get it to work, ideally id just like to know which number of 5s John would need to get to increase the average up by 0.5, this example gives me an answer of 147.8?

[shg]

Don't think so:

Row\Col	B	C	D	E	F
2	Rating	Count
3	5	20
4	4	5
5	3	6
6	2	2
7	1	1
8	Avg Rating	# Ratings
9	4.21	34			B9: =SUMPRODUCT(B3:B7, C3:C7) / SUM(C3:C7)
10	Tgt Avg
11	4.71				B11: =B9 + 0.5
12	Rating
13	5				B13: Input
14	Number of
15	57.80				B15: =$C$9 * ($B11 - $B$9) / (B$13 - $B11)