Weighted Average with different criterias

[msalas0308]

Hello,
I am trying to find the weighted average of 2 or more cells with different words. I can't seem to find the correct formula. I want to find the weighted average of everything with the word MA in it, in the Work Center column. I can't seem to get the correct formula.

Work Center	Snapshot	Total Capacity
RVR100MA	31.0	400.0
RVR101MA	102.0	80.0
RVR102MA	48.0	80.0
RVR103MA	60.0	80.0
RVR104MA	64.0	80.0
RVR108MA	90.6	80.0
RVR120MA	95.0	80.0
RVR160MM	134.5	240.0
RVR198MA	17.0	240.0
RVR199MA	0.0	40.0
RVR200MS	5.0	800.0
RVR201MS	92.5	80.0
RVR202MS	92.3	80.0
RVR203MS	64.0	80.0
RVR204MS	71.0	80.0
RVR205MS	78.0	80.0
RVR206MS	0.0	80.0
RVR209MS	0.0	80.0

 

[msalas0308]

Thank you all. It's still not the correct formula. I am trying to get the weighted average of RVR101MA, RVR102MA, RVR103MA, RVR104MA and RVR120MA together.

 

[Fluff]

What answer would you expect to get from the data you posted?

 

[msalas0308]

Sorry, I should have been more specific.

I am trying to find the weighted average of RVR101MA, RVR102MA, RVR103MA, RVR104MA and RVR120MA as a whole, in column "A" (Work Center) with column "C" (Total Capacity) as the weight. I want the formula to be able to look for everything with those words and calculate the weighted average based on that. Similar to the formulas you all have given me.

Book5
	A	B	C
1	Work center	Snapshot	Total Capacity
2	RVR100MA	31.0	400.0
3	RVR101MA	102.0	80.0
4	RVR102MA	48.0	80.0
5	RVR103MA	60.0	80.0
6	RVR104MA	64.0	80.0
7	RVR108MA	90.6	80.0
8	RVR120MA	95.0	80.0
9	RVR198MA	17.0	240.0
10	RVR199MA	0.0	40.0
11	RVR160MM	134.5	240.0
12	RVR200MS	5.0	800.0
13	RVR201MS	92.5	80.0
14	RVR202MS	92.3	80.0
15	RVR203MS	64.0	80.0
16	RVR204MS	71.0	80.0
17	RVR205MS	78.0	80.0
18	RVR206MS	0.0	80.0
19	RVR209MS	0.0	80.0
20	RVR210ST	98.0	240.0
21	RVR211ST	105.0	120.0
22	RVR220LB	333.0	360.0
23	RVR222LB	87.0	80.0
24	RVR230WE	156.0	120.0
25	RVR240IC	69.0	160.0
26	RVR250EO	381.0	200.0
27	RVR254AM	78.0	80.0
28	RVR260VT	157.0	120.0
29	RVR298MS	106.0	240.0
30	RVR300EL	424.2	400.0
31	RVR320IT	147.0	200.0
32	RVR340AN	327.0	360.0
33	RVR399IE	9.0	40.0
34	RVR500OP	4.0	40.0
35	RVR610EI	1.0	40.0
36	RVRPLNR	0.0	56.0
37	RVRSCED	0.0	56.0
Sheet1

 

[AhoyNC]

Try this small change to Fluff's formula.

SUMPRODUCT((ISNUMBER(MATCH(A2:A19,F2:F6,0)))*(B2:B19*C2:C19))/SUMPRODUCT(--(ISNUMBER(MATCH(A2:A19,F2:F6,0))),C2:C19)

Book1
	A	B	C	D	E	F
1	Work Center	Snapshot	Total Capacity			Match
2	RVR100MA	31	400	73.8		RVR101MA
3	RVR101MA	102	80			RVR102MA
4	RVR102MA	48	80			RVR103MA
5	RVR103MA	60	80			RVR104MA
6	RVR104MA	64	80			RVR120MA
7	RVR108MA	90.6	80
8	RVR120MA	95	80
9	RVR160MM	134.5	240
10	RVR198MA	17	240
11	RVR199MA	0	40
12	RVR200MS	5	800
13	RVR201MS	92.5	80
14	RVR202MS	92.3	80
15	RVR203MS	64	80
16	RVR204MS	71	80
17	RVR205MS	78	80
18	RVR206MS	0	80
19	RVR209MS	0	80
Sheet2

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT((ISNUMBER(MATCH(A2:A19,F2:F6,0)))*(B2:B19*C2:C19))/SUMPRODUCT(--(ISNUMBER(MATCH(A2:A19,F2:F6,0))),C2:C19)

 

[msalas0308]

@AhoyNC - That worked, thank you so much.

Thank you everyone for all the help with this one. It was driving me crazy.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[AhoyNC]

You're welcome. Fluff did most of the work on this one.

 

[msalas0308]

Thank you @Fluff