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

 

[XcelXpert]

Hello there!

I may have found a way to accomplish this. Let me know if this is what you are looking for.

I created another column (D) and labelled it as Weighted Average. In the cell D2, I used the formula

=IFERROR(IF(SEARCH("MA",A2:A2),AVERAGE(B2:C2),""),"")

It seems to calculate the correct average. It will also display a blank cell for any criteria that does not contain MA in column A.

Apologies if this is difficult to understand. I made an excel sheet to test this out, but I cannot figure out how to safely post it here. I tried doing that before, and the mods got mad at me.

 

[Fluff]

I made an excel sheet to test this out, but I cannot figure out how to safely post it here

You can use the XL2BB add-in.

 

[msalas0308]

What's an XL2BB add-in?

 

[Fluff]

Hi & welcome to MrExcel.
It's an add-in that enables members to post data to the board, like

+Fluff New.xlsm
	A	B	C	D
1	Work Center	Snapshot	Total Capacity
2	RVR100MA	31	400	5916.444
3	RVR101MA	102	80
4	RVR102MA	48	80
5	RVR103MA	60	80
6	RVR104MA	64	80
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
Results

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT((RIGHT(A2:A19,2)="MA")*(B2:B19*C2:C19))/SUMPRODUCT(--(RIGHT(A2:A19,2)="MA"))

 

[Fluff]

Another (slightly simpler) option would be

=SUMPRODUCT((RIGHT(A2:A19,2)="MA")*(B2:B19*C2:C19))/COUNTIFS(A2:A19,"*MA")

 

[msalas0308]

What if I want to exclude certain MA's to the formula? I just want RVR101MA, RVR102MA, RVR103MA, RVR104MA and RVR120MA.

 

[XcelXpert]

Hello!

I have figured out how to do this. I had to update the code a bit to make it work. Let me know if this is what you were looking for!

The new code is:

=(IF(COUNT(SEARCH({"RVR101MA","RVR102MA","RVR103MA","RVR104MA","RVR120MA"},A3:A3)),AVERAGE(B3,C3),""))

Also, I have no idea what a XL2BB add-in is.

 

[Fluff]

Also, I have no idea what a XL2BB add-in is.

Have a look here

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F
1	Work Center	Snapshot	Total Capacity
2	RVR100MA	31	400	5904		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
20
Results

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