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")

 

[Jon Peltier]

It looks like the weights are in column D not column C.

Also, why are you multiplying the values in column C by themselves (squaring them) in the denominator of the formula in column G?

 

[sparky2205]

Hi Jon,
apologies, the column headings are misleading. Values in the hashed cells are being used as interim calculation steps and will be hidden in the final worksheet to the user.
Column D (white cells) contains the weights used in the calculation of the values in F7, F10 & F13. I have used SUMPRODUCT for this and it works.
I am now trying to move on a level and get the weighted average of the values in F7, F10 & F13. This value will be displayed in G7.
The weights for this calculation will be in C7, C10 & C13. These are stripped from the text strings in B7, B10 & B13 respectively.
I cannot get SUMPRODUCT to work with this data as it is separated by other rows.
The formula currently in G7 is my attempt to use a deconstructed SUMPRODUCT to effectively attain the same result. This works fine except when either F7, F10 or F13 evaluates to N/A, which is a valid result for those cells. To account for this using my current formula I would have to write a monster nested IF statement. At least that's as far as my knowledge goes.
As an alternative I decided to bring that data together so that I could again use SUMPRODUCT. That is what is attempted in columns R & S above.
Using SUMPRODUCT on this data returned a value of 0. So I used the VALUE function to get SUMPRODUCT to recognise the values.
But introducing N/A as a value has the same impact here, SUMPRODUCT doesn't like it and I get an error.

Also, why are you multiplying the values in column C by themselves (squaring them) in the denominator of the formula in column G?
I had problems converting my SUMPRODUCT results into a percentage. I was trying to obtain a standard formula that I could use to achieve this. In a previous iteration of the spreadsheet I had used this and it worked so I was reluctant to mess with it. But then the parameters were changed and I had to do further manipulation. As you can see there is also a /7.5 in the denominator.

I hope it's clear what I'm trying to achieve but just in case I'm posting the full spreadsheet below and a recap.
I appreciate any help you can provide on this Jon.

What I'm trying to achieve:

In section - Management / Quality Culture / Training (20)
Level 1:
F7 should contain the weighted average of the Score in E8:E9 with the weights being in D8:D9
F10 shold contain the weighted average of the Score in E11:E12 with the weights being in D11:D12
F13 shold contain the weighted average of the Score in E14:E15 with the weights being in D14:D15
Level 2:
G7 should contain the weighted average of the Summary in F7, F10, F13 with the weights being in C7, C10, C13 respectively

In section - Improvements (20)
Level 1:
F20 should contain the weighted average of the Score in E21:E24 with the weights being in D21:D24
F25 should contain the weighted average of the Score in E26:E28 with the weights being in D26:D58
F29 should contain the weighted average of the Score in E30:E31 with the weights being in D30:D31
Level 2:
G20 should contain the weighted average of the Summary in F20, F25, F29 with the weights being in C20, C25, C29 respectively

This is the same for sections: Feedback (25), Facility (15) & Manufacturing (20)

Level 3: (Total Score):
G87 should contain the weighted average of the Section Score in G7, G20, G34, G58, G70 with the weights being in G16, G30, G53, G66, G83 respectively

Site Assessment Final.xlsx
	A	B	C	D	E	F	G
6	1	Management / Quality Culture / Training (20)	Assessment	Weight	Score	Summary	Section Score
7	1.1	Quality Policy and Objectives (5)	5	5	2	20%	20%
8	1.1.1	Quality Policy	1	2	0.2	0
9	1.1.2	Objectives	1	3	0.3	0.5
10	1.2	Management Review (5)	5	5	2	20%
11	1.2.1	Management Review Meeting	1	3	0.3	0
12	1.2.2	Management Review Action Items and Meeting Minutes	1	2	0.2	0.5
13	1.3	Training (10)	10	6	2	20%
14	1.3.1	Quality System On Boarding	1	3	0.3	0
15	1.3.2	SME	1	3	0.3	1
16	1.3.3	Procedure training (Corp and Site)	1	2	0.2		20
17	1.3.4	Manufacturing Training	1	2	0.2		0
18
19	2	Improvements (20)	Assessment	Weight	Score	Summary	Section Score
20	2.1	Corrective Action(10)	10	10	4	100%	100%
21	2.1.1	System Utilized	5	2	1	0
22	2.1.2	Investigation / Root Cause Documentation	5	2	1	5
23	2.1.3	Action Plan Developed Time	5	4	2
24	2.1.4	Completion Rate	5	2	1
25	2.2	Preventive Action (5)	5	5	3	100%
26	2.2.1	System Utilized	5	3	1.5	0
27	2.2.2	Investigation / Root Cause Documentation	5	1	0.5	2.5
28	2.2.3	Completion rate	5	1	0.5
29	2.3	Internal Audit program (5)	5	5	2	100%
30	2.3.1	Schedule	5	3	1.5	0	20
31	2.3.2	Training	5	2	1	2.5	0
32
33	3	Feedback (25)	Assessment	Weight	Score	Summary	Section Score
34	3.1	Recalls (5)	5	5	1	100%	100%
35	3.1.1	Recalls	5	5	2.5	0
36						2.5
37	3.2	Customer Complaints Z7 (5)	5	5	5	100%
38	3.2.1	Patient Confidentiality	5	1	0.5	0
39	3.2.2	System Utilized	5	1	0.5	2.5
40	3.2.3	Investigation / Root Cause Documentation	5	1	0.5
41	3.2.4	Corrective Action Plan Developed	5	1	0.5
42	3.2.5	Completion Rate	5	1	0.5
43	3.3	Product Complaints Z8 (Device) (5)	5	5	5	100%
44	3.3.1	Patient Confidentiality	5	1	0.5	0
45	3.3.2	System Utilized	5	1	0.5	2.5
46	3.3.3	Investigation / Root Cause Documentation	5	1	0.5
47	3.3.4	Corrective Action Plan Developed	5	1	0.5
48	3.3.5	Completion Rate	5	1	0.5
49	3.4	External Assessment (7)	7	7	3	100%
50	3.4.1	Customers	5	3	1.5	0
51	3.4.2	FDA	5	2	1	3.5
52	3.4.3	ISO & MDSAP	5	2	1
53	3.5	Customer Feedback (3)	3	3	1	100%	25
54	3.5.1	Customer Feedback Activities	5	3	1.5	0	0
55						1.5
56
57	4	Facility (15)	Assessment	Weight	Score	Summary	Section Score
58	4.1	Overall Facility Appearance (6)	6	6	3	100%	100%
59	4.1.1	Outside	5	2	1	0
60	4.1.2	Inside	5	2	1	3
61	4.1.3	Warehouse and material management	5	2	1
62	4.2	Controlled Area(s) Manufacturing(9)	9	9	5	100%
63	4.2.1	Appearance	5	2	1	0
64	4.2.2	Cleanroom Environmental Controls	5	2	1	4.5
65	4.2.3	Cleaning - Controlled Environment	5	2	1
66	4.2.4	Cleaning - equipment	5	1	0.5		15
67	4.2.5	Gowning Area and Material Entry	5	2	1		0
68
69	5	Manufacturing (20)	Assessment	Weight	Score	Summary	Section Score
70	5.1	Product Nonconformance (5)	5	5	4	100%	100%
71	5.1.1	System Utilized	5	1	0.5	0
72	5.1.2	Containment Action & Material Disposition	5	1	0.5	2.5
73	5.1.3	Investigation / Root Cause Documentation	5	2	1
74	5.1.4	Completion Rate	5	1	0.5
75	5.2	Production Controls (5)	5	5	4	100%
76	5.2.1	Monitoring	5	2	1	0
77	5.2.2	Sanctioned Countries Check	5	1	0.5	2.5
78	5.2.3	Material Compliance (Assent Compliance)	5	1	0.5
79	5.2.4	Export Controls	5	1	0.5
80	5.3	Process Yields (5)	5	5	1	100%
81	5.3.1	Yields	5	5	2.5	0
82						2.5
83	5.4	ERP Utilization (5)	5	5	1	100%	20
84	5.4.1	ERP System	5	5	2.5	0	0
85						2.5
86
87						Total Score	84%
Summary Sheet

Cell Formulas
Range	Formula
C7,C10,C13,C83,C80,C75,C70,C62,C58,C53,C49,C43,C37,C34,C29,C25,C20	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,D29	D7	=SUM(D8:D9)
E7,E10,E13,E29	E7	=COUNTA(E8:E9)
F7,F10,F29	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,F30	F8	=COUNTIF(E8:E9,"N/A")
F9,F12,F31	F9	=SUM(E8:E9)
C11:E12	C11	=MainSheet!H13
F13,F75,F70,F20	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,F76,F71,F21	F14	=COUNTIF(E14:E17,"N/A")
F15,F77,F72,F22	F15	=SUM(E14:E17)
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")
D20,D75,D70	D20	=SUM(D21:D24)
E20,E75,E70	E20	=COUNTA(E21:E24)
C21:E24	C21	=MainSheet!H27
G20	G20	=IF(G31=3,"N/A",((F20*C20)+(F25*C25)+(F29*C29))/(((C20*C20)+(C25*C25)+(C29*C29))/7.5))
D25,D58,D49	D25	=SUM(D26:D28)
E25,E58,E49	E25	=COUNTA(E26:E28)
F25,F58,F49	F25	=IF(OR(D25=0,E25=F26),"N/A",SUMPRODUCT(D26:D28,E26:E28)/(SUMPRODUCT(D26:D28,D26:D28)/2))
C26:E28	C26	=MainSheet!H34
F26,F59,F50	F26	=COUNTIF(E26:E28,"N/A")
F27,F60,F51	F27	=SUM(E26:E28)
C30:E31	C30	=MainSheet!H40
G30	G30	=IF((LEN(B19)-LEN(SUBSTITUTE(B19,"(","")))>1,MID(B19,(FIND("~",SUBSTITUTE(B19,"(","~",2))+1),(FIND("~",SUBSTITUTE(B19,")","~",2))-FIND("~",SUBSTITUTE(B19,"(","~",2))-1)),MID(B19,FIND("(",B19)+1,(FIND(")",B19)-(FIND("(",B19)+1))))
G31,G54	G31	=COUNTIF(F20,"N/A")+COUNTIF(F25,"N/A")+COUNTIF(F29,"N/A")
D34	D34	=SUM(D35:D35)
E34,E83,E80,E53	E34	=COUNTA(E35:E35)
C35:E35	C35	=MainSheet!H47
F34,F80,F83,F53	F34	=IF(OR(D34=0,E34=F35),"N/A",SUMPRODUCT(D35:D35,E35:E35)/(SUMPRODUCT(D35:D35,D35:D35)/2))
F35,F81,F84,F54	F35	=COUNTIF(E35,"N/A")
F36,F82,F85,F55	F36	=SUM(E35)
D37,D62,D43	D37	=SUM(D38:D42)
E37,E62,E43	E37	=COUNTA(E38:E42)
F37,F62,F43	F37	=IF(OR(D37=0,E37=F38),"N/A",SUMPRODUCT(D38:D42,E38:E42)/(SUMPRODUCT(D38:D42,D38:D42)/2))
C38:E42	C38	=MainSheet!H51
F38,F63,F44	F38	=COUNTIF(E38:E42,"N/A")
F39,F64,F45	F39	=SUM(E38:E42)
C44:E48	C44	=MainSheet!H59
C50:E52	C50	=MainSheet!H67
D53,D83,D80	D53	=SUM(D54)
C54:E54	C54	=MainSheet!H73
G34	G34	=IF(G54=5,"N/A",((F34*C34)+(F37*C37)+(F43*C43)+(F49*C49)+(F53*C53))/(((C34*C34)+(C37*C37)+(C43*C43)+(C49*C49)+(C53*C53))/5.32))
G53	G53	=IF((LEN(B33)-LEN(SUBSTITUTE(B33,"(","")))>1,MID(B33,(FIND("~",SUBSTITUTE(B33,"(","~",2))+1),(FIND("~",SUBSTITUTE(B33,")","~",2))-FIND("~",SUBSTITUTE(B33,"(","~",2))-1)),MID(B33,FIND("(",B33)+1,(FIND(")",B33)-(FIND("(",B33)+1))))
C59:E61	C59	=MainSheet!H79
C63:E67	C63	=MainSheet!H85
G58	G58	=IF(G67=2,"N/A",((F58*C58)+(F62*C62))/(((C58*C58)+(C62*C62))/7.8))
G66	G66	=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))))
G67	G67	=COUNTIF(F58,"N/A")+COUNTIF(F62,"N/A")
C71:E74	C71	=MainSheet!H95
C76:E79	C76	=MainSheet!H102
C81:E81	C81	=MainSheet!H109
C84:E84	C84	=MainSheet!H113
G70	G70	=IF(G84=4,"N/A",((F70*C70)+(F75*C75)+(F80*C80)+(F83*C83))/(((C70*C70)+(C75*C75)+(C80*C80)+(C83*C83))/5))
G83	G83	=IF((LEN(B69)-LEN(SUBSTITUTE(B69,"(","")))>1,MID(B69,(FIND("~",SUBSTITUTE(B69,"(","~",2))+1),(FIND("~",SUBSTITUTE(B69,")","~",2))-FIND("~",SUBSTITUTE(B69,"(","~",2))-1)),MID(B69,FIND("(",B69)+1,(FIND(")",B69)-(FIND("(",B69)+1))))
G84	G84	=COUNTIF(F70,"N/A")+COUNTIF(F75,"N/A")+COUNTIF(F80,"N/A")+COUNTIF(F83,"N/A")
G87	G87	=((G16*G7)+(G30*G20)+(G53*G34)+(G66*G58)+(G83*G70))/(((G16*G16)+(G30*G30)+(G53*G53)+(G66*G66)+(G83*G83))/20.5)

 

[Jon Peltier]

Column F formulas are SUMPRODUCT. For example, F7:

=SUMPRODUCT(E8:E9,D8:D9)/SUM(D8:D9)

Column G formulas are sums of products. For example, G7:

=(F7*C7+F10*C10+F13*C13)/(C7+C10+C13)

 

[sparky2205]

Hi Jon,
the SUMPRODUCT formula works fine.
I had started off with something like your second formula: =(F7*C7+F10*C10+F13*C13)/(C7+C10+C13)
This works fine as long as either F7, F10 or F13 are not N/A. Then it returns an error.

 

[Jon Peltier]

=(IFNA(F7,0)*C7+IFNA(F10,0)*C10+IFNA(F13,0)*C13)/(C7+C10+C13)

 

[sparky2205]

That doesn't work because N/A is text not #N/A error.

 

[Jon Peltier]

=(N(F7)*C7+N(F10)*C10+N(F13)*C13)/(C7+C10+C13)

 

[sparky2205]

That's giving me a result Jon, but not the correct result.
In my data, if one subsection e.g. F7 is N/A, then the other two subsections i.e. F10 and F13 now make up 100%.
So with F7 = N/A and F10 and F13 both = 100%, G7 should return 100%. Your formula returns 75%.

 

[Jon Peltier]

=(N(F7)*C7+N(F10)*C10+N(F13)*C13)/(ISNUMBER(F7)*C7+ISNUMBER(F10)*C10+ISNUMBER(F13)*C13)