Weighted Average as %

[sparky2205]

Hi folks,
I have the following data:

Site Assessment Current Test Version.xlsx
	B	C	D	E	F	G
6	Management / Quality Culture / Training (20)	Assessment	Weight	Score	Summary	Section Score
7	Quality Policy and Objectives (5)	5
8	Quality Policy	0	0	2.5
9	Objectives	0	2	2.5
10	Management Review (5)	5
11	Management Review Meeting	0	0	2.5
12	Management Review Action Items and Meeting Minutes	0	0	2.5
13	Training (10)	10			1.56818
14	Quality System On Boarding	2.5	5	1.25
15	SME	0	3	1.5
16	Procedure training (Corp and Site)	0	2	2
17	Manufacturing. Training	0	1	2.5
Summary Sheet (2)

Cell Formulas
Range	Formula
C8:E9	D8	='Main Sheet'!J8
C11:E12	D11	='Main Sheet'!J13
C7,C10,C13	C7	=MID(B7,FIND("(",B7)+1,FIND(")",B7)-(FIND("(",B7)+1))
F13	F13	=SUMPRODUCT(D14:D17,E14:E17)/SUM(D14:D17)
C14:E17	C14	='Main Sheet'!I18

I'm trying to achieve a weighted average for each section under Summary i.e. F7 will contain the weighted average for Management / Quality Culture / Training, F10 will contain the weighted average for Management Review and F13 will contain the weighted average for Training. I need to express the result as a percentage of the overall weight for each section e.g. Training = 10.

Now the tricky bit (at least for me):

• The data can contain zero or N/A. Zero is a valid entry and is part of the data. N/A must be ignored. i.e. in the case of Training there are 4 sub categories. If one of these categories is N/A (Score) it is excluded from the calculation and the other 3 categories now make up a max possible 100%. If 2 are N/A then the remaining 2 make up a max possible 100% etc.
• At this point it hasn't been decided what way the scoring and weights will be recorded. In my data I have chosen a max score of 2.5 for each subcategory for convenience (10/4) but this could change. Regardless of the actual score I'm looking for the weighted average of each section as a percentage of the weight for that section based on individual subcategory scores and weights. I hope that makes sense.
• I've used SUMPRODUCT in F13 but I don't know how to convert this into a representative percentage of the weight for that subcategory.
• I then need to roll the 3 Summary scores up into the Section Score.
• Then there are multiple sections which will be rolled up into the Overall Score.

I'm hoping that's clear enough for someone to understand but if more info is needed let me know.

Thanks.

 

[sijpie]

Well your average calculation is very simple:

=AVERAGEIF(Range_to_check, Criterium, Range_to_average)
You don't mention which column the N/A is entered into. But say that is done in column C, and column E needs to be averaged then for training the formula would be:

=AVERAGEIF(C14:C17,"<>N/A",E14:E17)

So the formula checks in range C14:C17 if a cell contains N/A (not case sensitive) and then takes the average of any cells that do conform to '<>N/A'

 

[sparky2205]

Hi, thanks for your time.
column E (Score) is what I am looking for the weighted average of based on the values in column D (weight).
The N/A will be in column E (Score) so based on that I think your formula would work the same as =AVERAGEIF(E14:E17,"<>N/A")
However I don't understand how this gets me the weighted average of column E based on the weights in column D.

This is the formula I am using for the weighted average: =SUMPRODUCT(D14:D17,E14:E17)/SUM(D14:D17)
But I am having trouble expressing this as a percentage of the max allowable weight for the category e.g. Training = 10.

 

[sijpie]

Ah, you are wondering about the statistics.

Well I am not a statistics expert. But your calculation in F14 is correct for the weighted average of the training section. SO you can do the same for Management Review and Quality Policy. But at the moment a number of weightings are set to 0, that can't be correct.

Now for the roll up of these scores, that is more complicated. Because averages of averages are pretty meaningless. If I am not wrong (after testing with some numbers), the section score should be calculated as
=(C7*F7+C10*F10+C13*F13)/(C7+C10+C13)

 

[sparky2205]

Hi folks,
I'm just giving this a bump in case there is someone who hasn't seen it before who might be able to help.
As a recap, just focusing on the Summary Value for Training in F13:
I need the weighted average for the 4 sub categories within Training.
The Assessment column can be ignored.
The weighted average must be expressed as a % of the overall Training score i.e. 10. In this example I have assigned a max score of 2.5 to each sub category (10/4).
0 (zero) is a valid score, meaning either that value hasn't been entered yet or a score of 0 has been achieved.
But the weight and score can also be N/A i.e. that subcategory isn't applicable for this site. In this case the remaining 3 subcategories make up 100% i.e. the N/A is ignored.
I can't figure out how to convert the weighted average into a % that will take all these factors into account.
I've tried using variations on sijpie's suggestion above but this doesn't give me a weighted average.
I've also tried numerous variations on SUMPRODUCT, without success.

As an example F13 in the data below should be 100%.

Site Assessment Current Test Version.xlsx
	B	C	D	E	F	G
6	Management / Quality Culture / Training (20)	Assessment	Weight	Score	Summary	Section Score
7	Quality Policy and Objectives (5)	5	2	2	100%	78%
8	Quality Policy	0	0	2.5	0
9	Objectives	0	2	2.5
10	Management Review (5)	5	2	2	100%
11	Management Review Meeting	0	0	2.5	0
12	Management Review Action Items and Meeting Minutes	0	0	2.5
13	Training (10)	10	4	3	33%
14	Quality System On Boarding	0	N/A	N/A	1
15	SME	0	2	2.5
16	Procedure training (Corp and Site)	0	3	2.5
17	Manufacturing. Training	0	4	2.5	3
Summary Sheet

Cell Formulas
Range	Formula
C7,C13,C10	C7	=MID(B7,FIND("(",B7)+1,FIND(")",B7)-(FIND("(",B7)+1))
D7,D10	D7	=COUNTA(E8:E9)
E7,E10	E7	=COUNTIFS(E8:E9,"<>0",E8:E9,"<>N/A")
F7,F10	F7	=IF(D7=F8,"N/A",IF(E7<>0,SUM(E8:E9)/((C7/D7)*(D7-F8)),0))
C8:E9	C8	='Main Sheet'!I8
F8,F11	F8	=COUNTIF(E8:E9,"N/A")
G7	G7	=IF(F17=0,"N/A",SUM(F7,F10,F13)/F17)
C11:E12	C11	='Main Sheet'!I13
D13	D13	=COUNTA(E14:E17)
E13	E13	=COUNTIFS(E14:E17,"<>0",E14:E17,"<>N/A")
F13	F13	=SUMPRODUCT(--(D14:D17<>"N/A"),--(E14:E17<>"N/A"))/SUM(D14:D17)
C14:E17	C14	='Main Sheet'!I18
F14	F14	=COUNTIF(E14:E17,"N/A")
F17	F17	=COUNT(F7,F10,F13)

Any ideas, suggestions, comments would be very welcome. Please ask for clarifications if my information isn't clear enough.

 

[sijpie]

F13: =C13*(COUNTA(E14:E17)/E13)*SUM(E14:E17)

 

[sparky2205]

Hi sijpie,
apologies for the tardy reply, I was off work for a few days. Thanks for the response.
Your formula doesn't take the weighted average into account.
i.e. (using your formula) if we reduce the score in E14 to 2 the result in F13 will be 95% If we reduce the score in E17 to 2 the result in F13 is still 95%.
However, as the weighting for E17 is 4 (D17) and the weighting for E14 is 1 (D14) the impact of the score reduction in E17 should have a greater impact on the result in F13.
Any idea how the weightings can be incorporated into your formula?

 

[sijpie]

=SUMPRODUCT(D14:D17,E14:E17)/(SUM(D14:D17)*2.5)

That is assuming that for each part of the training the max score is 2.5