Weighted Average

[sparky2205]

Hi folks,
I'm trying to calculate a weighted average for a set of data.
I've already used SUMPRODUCT successfully in another part of the spreadsheet but now my date is not in a continuous range so I'm struggling with it.
I have tried 2 approaches:

Attempt 1:
Breaking up the essence of the SUMPRODUCT formula into something like this:
=IF(G17=3,"N/A",((F7*C7)+(F10*C10)+(F13*C13))/(((C7*C7)+(C10*C10)+(C13*C13))/7.5))

Attempt 2:
Creating a separate list with links to the data so the data is in order and I can use SUMPRODUCT.
That creates its own issues. I'm having to use the VALUE function in order for SUMPRODUCT to return a value. Otherwise it returns 0.
The I'm having issues as it is possible to have N/A (text, not error) in the data which causes SUMPRODUCT to return #VALUE!

I'm posting my attempts here, there are a few so excuse the messy bits.

What I'm trying to achieve:
In G7 I want the weighted average of - F7, F10, F13 with the weights being in - C7, C10, C13 and the result expressed as a percent.

G7 currently relates to Attempt 1 above
R9 currently relates to Attempt 2 above

I appreciate any help.

Site Assessment Final.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
4		Assessed By:	UPDATE INFORMATION															Weight	Score		Weight	Score
5									IF(G17=3,"N/A",((F7*C7)+(F10*C10)+(F13*C13))/(((C7*C7)+(C10*C10)+(C13*C13))/7.5))									5	N/A		5	1
6	1	Management / Quality Culture / Training (20)	Assessment	Weight	Score	Summary	Section Score											5	100%		5	1
7	1.1	Quality Policy and Objectives (5)	5	0	2	N/A	#VALUE!		#VALUE!									10	100%		5	1
8	1.1.1	Quality Policy	N/A	N/A	N/A	2			#VALUE!									#VALUE!			7	1
9	1.1.2	Objectives	N/A	N/A	N/A	0												#VALUE!			3	1
10	1.2	Management Review (5)	5	5	2	100%															125%
11	1.2.1	Management Review Meeting	5	3	1.5	0
12	1.2.2	Management Review Action Items and Meeting Minutes	5	2	1	2.5													#VALUE!
13	1.3	Training (10)	10	6	2	100%
14	1.3.1	Quality System On Boarding	5	3	1.5	0
15	1.3.2	SME	5	3	1.5	5
16	1.3.3	Procedure training (Corp and Site)	5	2	1		20
17	1.3.4	Manufacturing Training	5	2	1		1
Summary Sheet

Cell Formulas
Range	Formula
C4	C4	=MainSheet!C3:F3
C7,C10,C13	C7	=IF((LEN(B7)-LEN(SUBSTITUTE(B7,"(","")))>1,MID(B7,(FIND("~",SUBSTITUTE(B7,"(","~",2))+1),(FIND("~",SUBSTITUTE(B7,")","~",2))-FIND("~",SUBSTITUTE(B7,"(","~",2))-1)),MID(B7,FIND("(",B7)+1,(FIND(")",B7)-(FIND("(",B7)+1))))
D7,D10,D13	D7	=SUM(D8:D9)
E7,E10,E13	E7	=COUNTA(E8:E9)
F7,F10	F7	=IF(OR(D7=0,E7=F8),"N/A",SUMPRODUCT(D8:D9,E8:E9)/(SUMPRODUCT(D8:D9,D8:D9)/2))
C8:E9	C8	=MainSheet!H8
F8,F11	F8	=COUNTIF(E8:E9,"N/A")
F9,F12	F9	=SUM(E8:E9)
C11:E12	C11	=MainSheet!H13
F13	F13	=IF(OR(D13=0,E13=F14),"N/A",SUMPRODUCT(D14:D17,E14:E17)/(SUMPRODUCT(D14:D17,D14:D17)/2))
C14:E17	C14	=MainSheet!H18
F14	F14	=COUNTIF(E14:E17,"N/A")
F15	F15	=SUM(E14:E17)
S5	S5	=F7
S6	S6	=F10
S7	S7	=F13
I7	I7	=IF(G17=3,"N/A",((F7*C7)+(F10*C10)+(F13*C13))/(((C7*C7)+(C10*C10)+(C13*C13))/7.5))
I8	I8	=IF(G17=3,"N/A",((F7*C7)+(F10*C10)+(F13*C13))/(((C7*C7)+(C10*C10)+(C13*C13))/7.5))
R5	R5	=C7
R6	R6	=C10
R7	R7	=C13
R8	R8	=SUMPRODUCT(INT(R5:R7),INT(S5:S7))/20
R9	R9	=(SUMPRODUCT(VALUE(R5:R7)*(VALUE(S5:S7<>"N/A")))/((SUMPRODUCT(VALUE(R5:R7),VALUE(R5:R7)))/7.5))
V5	V5	=F34
V6	V6	=F37
V7	V7	=F43
V8	V8	=F49
V9	V9	=F53
U5	U5	=C34
U6	U6	=C37
U7	U7	=C43
U8	U8	=C49
U9	U9	=C53
U10	U10	=SUMPRODUCT(INT(U5:U9),INT(V5:V9))/20
S12	S12	=INT(S5)
G7	G7	=IF(G17=3,"N/A",((F7*C7)+(F10*C10)+(F13*C13))/(((C7*C7)+(C10*C10)+(C13*C13))/7.5))
G16	G16	=IF((LEN(B6)-LEN(SUBSTITUTE(B6,"(","")))>1,MID(B6,(FIND("~",SUBSTITUTE(B6,"(","~",2))+1),(FIND("~",SUBSTITUTE(B6,")","~",2))-FIND("~",SUBSTITUTE(B6,"(","~",2))-1)),MID(B6,FIND("(",B6)+1,(FIND(")",B6)-(FIND("(",B6)+1))))
G17	G17	=COUNTIF(F7,"N/A")+COUNTIF(F10,"N/A")+COUNTIF(F13,"N/A")

 

[sparky2205]

Beautiful John, works perfectly.
Thanks very much for your help.

 

[sparky2205]

Hi Jon,
you pointed out in an earlier post: "Also, why are you multiplying the values in column C by themselves (squaring them) in the denominator of the formula in column G?"
I did this because it was the only way I could get the formula to work. But I don't understand why it is working when I construct it that way or now, in fact, if it is working correctly.
My understanding of how SUMPRODUCT works when the weights <> 100 is: =SUMPRODUCT(Range1:Range2)/Range1 where Range 1 are the weights.
However, in my case, the result must be expressed as a percentage and this isn't working. In my example below F61 contains my modified formula while H61 contains the "correct" formula.
As you can see F61 = 100%. H61 = 133%.
Also if I change the score in E62, i.e. reduce the overall score, H61 returns a higher score.
F61 does return a lower score, as expected, but I'm not sure it is still the correct score.
As the weightings that contribute to this score are: 2,2,2,1,2 I would be expecting a drop of 22% in the overall score in F61 i.e. (100/9)*2=22. But as you can see from F61 I am getting a drop of 24%.

I hope that's not too rambling Jon and that you have enough information to assist me again.
Please let me know if you need any clarifications.

Site Assessment Final Clean Short Version.xlsx
	A	B	C	D	E	F	G	H
56	4	Facility (15)	Assessment	Weight	Score	Summary	Section Score
57	4.1	Overall Facility Appearance (6)				100%	86%
58	4.1.1	Outside	10	2	2
59	4.1.2	Inside	10	2	2
60	4.1.3	Warehouse and material management	10	2	2
61	4.2	Controlled Area(s) Manufacturing (9)				76%		144%
62	4.2.1	Appearance	0	2	0
63	4.2.2	Cleanroom Environmental Controls	10	2	2
64	4.2.3	Cleaning - Controlled Environment	10	2	2
65	4.2.4	Cleaning - equipment	10	1	1
Summary Sheet

Cell Formulas
Range	Formula
C57,C61	C57	=IF((LEN(B57)-LEN(SUBSTITUTE(B57,"(","")))>1,MID(B57,(FIND("~",SUBSTITUTE(B57,"(","~",2))+1),(FIND("~",SUBSTITUTE(B57,")","~",2))-FIND("~",SUBSTITUTE(B57,"(","~",2))-1)),MID(B57,FIND("(",B57)+1,(FIND(")",B57)-(FIND("(",B57)+1))))
D57	D57	=SUM(D58:D60)
E57	E57	=COUNTA(E58:E60)
F57	F57	=IF(OR(D57=0,E57=F58),"N/A",SUMPRODUCT(D58:D60,E58:E60)/(SUMPRODUCT(D58:D60,D58:D60)))
C58:E60	C58	=MainSheet!H79
F58	F58	=COUNTIF(E58:E60,"N/A")
G57	G57	=(N(F57)*C57+N(F61)*C61)/(ISNUMBER(F57)*C57+ISNUMBER(F61)*C61)
D61	D61	=SUM(D62:D66)
E61	E61	=COUNTA(E62:E66)
F61	F61	=IF(OR(D61=0,E61=F62),"N/A",SUMPRODUCT(D62:D66,E62:E66)/(SUMPRODUCT(D62:D66,D62:D66)))
C62:E65	C62	=MainSheet!H85
F62	F62	=COUNTIF(E62:E66,"N/A")
H61	H61	=IF(OR(D61=0,E61=F62),"N/A",SUMPRODUCT(D62:D66,E62:E66)/(SUM(D62:D66)))

 

[Jon Peltier]

Just to clarify, the weighted average is given by

=SUMPRODUCT(Range1,Range2)/SUM(Range1)

which is SUMPRODUCT divided by SUM, or

=SUMPRODUCT(Weights,Amounts)/SUM(Weights)

What looks funny is formulas like the one in F57, SUMPRODUCT divided by SUMPRODUCT:

=IF(OR(D57=0,E57=F58),"N/A",SUMPRODUCT(D58:D60,E58:E60)/(SUMPRODUCT(D58:D60,D58:D60)))

I think you want to use this instead, SUMPRODUCT divided by SUM:

=IF(OR(D57=0,E57=F58),"N/A",SUMPRODUCT(D58:D60,E58:E60)/(SUM(D58:D60)))

 

[sparky2205]

Hi Jon, thanks for your time again.
What you're describing above is what I've done in post #12. I am recreating it below with the expected value of 100%.
If you look at F61 you will see I have used the "strange" formula: =IF(OR(D61=0,E61=F62),"N/A",SUMPRODUCT(D62:D66,E62:E66)/(SUMPRODUCT(D62:D66,D62:D66)))
In H61 I have used what would conventionally be called the correct formula: =IF(OR(D61=0,E61=F62),"N/A",SUMPRODUCT(D62:D66,E62:E66)/(SUM(D62:D66)))
i.e. =SUMPRODUCT(Weights,Amounts)/SUM(Weights)
But as you can see this doesn't evaluate properly.
Looking at the data below:
H61 should = 100% but instead I get 189% but I don't understand why.

Site Assessment Final Clean Short Version.xlsx
	A	B	C	D	E	F	G	H
56	4	Facility (15)	Assessment	Weight	Score	Summary	Section Score
57	4.1	Overall Facility Appearance (6)				100%	100%
58	4.1.1	Outside	10	2	2
59	4.1.2	Inside	10	2	2
60	4.1.3	Warehouse and material management	10	2	2
61	4.2	Controlled Area(s) Manufacturing (9)				100%		189%
62	4.2.1	Appearance	10	2	2
63	4.2.2	Cleanroom Environmental Controls	10	2	2
64	4.2.3	Cleaning - Controlled Environment	10	2	2
65	4.2.4	Cleaning - equipment	10	1	1
66	4.2.5	Gowning Area and Material Entry	10	2	2
Summary Sheet

Cell Formulas
Range	Formula
C57,C61	C57	=IF((LEN(B57)-LEN(SUBSTITUTE(B57,"(","")))>1,MID(B57,(FIND("~",SUBSTITUTE(B57,"(","~",2))+1),(FIND("~",SUBSTITUTE(B57,")","~",2))-FIND("~",SUBSTITUTE(B57,"(","~",2))-1)),MID(B57,FIND("(",B57)+1,(FIND(")",B57)-(FIND("(",B57)+1))))
D57	D57	=SUM(D58:D60)
E57	E57	=COUNTA(E58:E60)
F57	F57	=IF(OR(D57=0,E57=F58),"N/A",SUMPRODUCT(D58:D60,E58:E60)/(SUMPRODUCT(D58:D60,D58:D60)))
C58:E60	C58	=MainSheet!H79
F58	F58	=COUNTIF(E58:E60,"N/A")
D61	D61	=SUM(D62:D66)
E61	E61	=COUNTA(E62:E66)
F61	F61	=IF(OR(D61=0,E61=F62),"N/A",SUMPRODUCT(D62:D66,E62:E66)/(SUMPRODUCT(D62:D66,D62:D66)))
C62:E66	C62	=MainSheet!H85
F62	F62	=COUNTIF(E62:E66,"N/A")
H61	H61	=IF(OR(D61=0,E61=F62),"N/A",SUMPRODUCT(D62:D66,E62:E66)/(SUM(D62:D66)))
G57	G57	=(N(F57)*C57+N(F61)*C61)/(ISNUMBER(F57)*C57+ISNUMBER(F61)*C61)
G66	G66	=IF((LEN(B56)-LEN(SUBSTITUTE(B56,"(","")))>1,MID(B56,(FIND("~",SUBSTITUTE(B56,"(","~",2))+1),(FIND("~",SUBSTITUTE(B56,")","~",2))-FIND("~",SUBSTITUTE(B56,"(","~",2))-1)),MID(B56,FIND("(",B56)+1,(FIND(")",B56)-(FIND("(",B56)+1))))

 

[Jon Peltier]

What is each percent value a percentage of? The maximum possible of each value? That's probably the issue. And maybe you should turn each value in column E into a percentage before doing the SUMPRODUCT.

 

[sparky2205]

I got this sorted Jon. I'ts now in a condition I'm happy with.
Thanks again for all your help. A bit of learning/relearning for me as well.
I have only come across the N function once before and I had forgotten it.
Have a good weekend.