Weighted Average

[elicayan]

Can you please assist me in getting the weighted average of the following data? This is for a 9 day performance and I wanted to rank them accordingly with their performance. Thank you!

Evaluator	Jameel	Jameel	Jameel	Jameel	Jameel			Fahad	Fahad	Fahad	Fahad		Average
Date	5/4/2014	5/5/2014	5/6/2014	5/7/2014	5/8/2014	5/9/2014	5/10/2014	5/11/2014	5/12/2014	5/13/2014	5/14/2014	5/15/2014
Job Knowledge	3	Good in knowledge	3		3		2		3				5	Travel Case	4	Travel Request	4	Checking AWB	5	Card Replacement			3.56
Speed on System	1	No system practice yet	2		2		1		2				4		3		3		4				2.44
Questions Asked	1	Very quiet person	1	No questions asked	1	Very quiet person	1	No questions asked	2	No questions asked			2		4		2		2				1.78
Causing Distraction	5	Focused all the time	5	Focused all the time	4	Focused all the time	4	No distractions - focused all the time	4				1		1		1		1				2.89
Breaks	4		4		4		4	Min breaks	4				1		1		1		1				2.67
Confidence	2		3	Matouk made 2 IVR on his own	3		2	Need to gain more confidence	3				4		4		4		4				3.22
Productive	1		3	Professional and reliable	4	Very professional RM	3		4				4		3		4		4				3.33
Remarks				First day on system - Fair		He was trying for his accent to avoid miscommunications		Matouk language accent might be an issue		Needs to work on his accent and his communication skills in Arabic
																						Total	2.84

<tbody>
</tbody>

 

[dirkgeraedts]

I am willing to help you out but what or where are the weights?

Dirk

 

[elicayan]

thank you dirk! but basically there is no given weight for each category it's just that they are given a rating between 1 being the lowest and 5 highest. now if I average everything it will be average of the average for the rating/ranking in which I don't want that because that wouldn't be accurate

 

[Coryj5]

thank you dirk! but basically there is no given weight for each category it's just that they are given a rating between 1 being the lowest and 5 highest. now if I average everything it will be average of the average for the rating/ranking in which I don't want that because that wouldn't be accurate

Correct, So you average the SUM of each category. AVERAGE(SUM(A2,A4,A6,A8),SUM(B2,B4,B6,B8)...etc)))

 

[Coryj5]

And it may be faster to simply add a formula to the end of column A and write it out once, then use the fillerbar and average that ending column. IT would take a while to select all the cells for sum in the formula since they are not a consecutive range.

 

[elicayan]

so the formula would be =average(sum(A2,A4,A6,A8,A10,A12,A14)/sum(B2 etc) or horizontally or vertically?

 

[Coryj5]

or CSE function Average(B3:B9+D3:D9+F3:F9+H3:H9+J3:J9) and remember to press Ctl+Shift+Enter

 

[elicayan]

I have tried =sumproduct(a2:a14,b2:b14)/sum(b2:b14) but it's giving me an error or #value in excel what I wanted is to get the overall performance rating

 

[Coryj5]

No need to divide by SUM. The average is doing that for you. You want to add the values for each category and then average the totals for a final average score. Either formula will work in your example. The CSE formula is easier.

 

[Coryj5]

You appear to have some merged cells there, so be aware of the cells you are adding. Your sumproduct formula there is multiplying numbers and letters form the example above and then arriving at a sum of those values. That is not the correct math even if you are referencing the proper cells. You want to add the total scores per category and average the totals together.