Conditional Weighted Average

[lager1001]

Need help with a weighted average formula. I've tried AverageIfs but get #DIV/0 errors because some of the numbers being calculated in the average are "0".

See below example.

There are 4 unique accounts (but 7 line items) in CW24, each with their respective Score. If I do a simple average, it takes all scores for all line items and I get 63.86. What I need is the weighted average so it calculates each score only once for each account and provide the average by the unique number of accounts for the calendar week. The desired outcome is the "Weighted Average Column" which would be 70.39+65.45+99.96+0.00 / 4. Some of my scores are 0.00 so these have to be inclusive to the formula. Can anyone assist?

Year/CW	Account	Score	Average Score	Weighted Average
2020-CW24	ABC	70.39	63.86	58.95
2020-CW24	ABC	70.39	63.86	58.95
2020-CW24	ABC	70.39	63.86	58.95
2020-CW24	ABC	70.39	63.86	58.95
2020-CW24	DEF	65.45	63.86	58.95
2020-CW24	GHI	99.96	63.86	58.95
2020-CW24	JKL	0.00	63.86	58.95

 

[CA_Punit]

=AVERAGE(IFERROR(C2:C8/IF(FREQUENCY(MATCH(B2:B8,B2:B8,0),MATCH(B2:B8,B2:B8,0))>0,1,0),FALSE))

 

[lager1001]

Hi
Thanks for the formula. I need this to include the Year/CW column as well. I tried adding it as a MATCH factor, but it is giving me the same results as the normal Average function. There are hundreds of accounts with various scores over the CWs throughout the year. That is the goal here. Is to get the weighted average per CW for each account.

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G	H
1	Year/CW	Account	Score
2	2020-CW24	ABC	70.39
3	2020-CW24	ABC	70.39				2020-CW24	67.5875
4	2020-CW24	ABC	70.39				2020-CW25	158.96
5	2020-CW24	ABC	70.39
6	2020-CW24	DEF	100
7	2020-CW24	GHI	99.96
8	2020-CW24	JKL	0
9	2020-CW25	ABC	70.39
10	2020-CW25	ABC	70.39
11	2020-CW25	ABC	70.39
12	2020-CW25	ABC	70.39
13	2020-CW25	DEF	65.45
14	2020-CW25	GHI	500
15	2020-CW25	JKL	0
Sheet1

Cell Formulas
Range	Formula
H3:H4	H3	=AVERAGE(IF(IF(FREQUENCY(MATCH($A$2:$A$15&$B$2:$B$15,$A$2:$A$15&$B$2:$B$15,0),MATCH($A$2:$A$15&$B$2:$B$15,$A$2:$A$15&$B$2:$B$15,0))>0,TRUE,FALSE)=TRUE,IF($A$2:$A$15=G3,$C$2:$C$15),FALSE))

 

[lager1001]

Thanks again. But how can this be made dynamic? Each report will have different numbers of rows. Can I not use dynamic table header names instead of cells? I'm unable to get this to work in this format. I need the average to be in every row for every CW and Account regardless of how many times that Account appears (see original example).

 

[CA_Punit]

Book1
	A	B	C	D
1	Year/CW	Account	Score
2	2020-CW24	ABC	70.39	67.5875
3	2020-CW24	ABC	70.39	67.5875
4	2020-CW24	ABC	70.39	67.5875
5	2020-CW24	ABC	70.39	67.5875
6	2020-CW24	DEF	100	67.5875
7	2020-CW24	GHI	99.96	67.5875
8	2020-CW24	JKL	0	67.5875
9	2020-CW25	ABC	70.39	158.96
10	2020-CW25	ABC	70.39	158.96
11	2020-CW25	ABC	70.39	158.96
12	2020-CW25	ABC	70.39	158.96
13	2020-CW25	DEF	65.45	158.96
14	2020-CW25	GHI	500	158.96
15	2020-CW25	JKL	0	158.96
Sheet1

Cell Formulas
Range	Formula
D2:D15	D2	=AVERAGE(IF(IF(FREQUENCY(MATCH($A$2:$A$15&$B$2:$B$15,$A$2:$A$15&$B$2:$B$15,0),MATCH($A$2:$A$15&$B$2:$B$15,$A$2:$A$15&$B$2:$B$15,0))>0,TRUE,FALSE)=TRUE,IF($A$2:$A$15=A2,$C$2:$C$15),FALSE))

Use Named Ranged instead of cell reference in case your ranges are dynamic in nature.

 

[Fluff]

If you convert your data to a structured table you can use it like

+Fluff New.xlsm
	A	B	C	D
1	Year/CW	Account	Score	Weighted Average
2	2020-CW24	ABC	70.39	58.95
3	2020-CW24	ABC	70.39	58.95
4	2020-CW24	ABC	70.39	58.95
5	2020-CW24	ABC	70.39	58.95
6	2020-CW24	DEF	65.45	58.95
7	2020-CW24	GHI	99.96	58.95
8	2020-CW24	JKL	0	58.95
9	2020-CW25	ABC	70.39	158.96
10	2020-CW25	ABC	70.39	158.96
11	2020-CW25	ABC	70.39	158.96
12	2020-CW25	ABC	70.39	158.96
13	2020-CW25	DEF	65.45	158.96
14	2020-CW25	GHI	500	158.96
15	2020-CW25	JKL	0	158.96
sheet2

Cell Formulas
Range	Formula
D2:D15	D2	=AVERAGE(IF(IF(FREQUENCY(MATCH([Year/CW]&[Account],[Year/CW]&[Account],0),MATCH([Year/CW]&[Account],[Year/CW]&[Account],0))>0,TRUE,FALSE)=TRUE,IF([Year/CW]=[@[Year/CW]],[Score]),FALSE))

 

[lager1001]

Hmmm. Entered this exactly, and it pulls the same average for every line regardless of calendar week or account. Not sure what I'm doing wrong.

 

[Fluff]

You probably need to confirm the formula with Ctrl Shift Enter, rather than just enter & then fill it down.

 

[lager1001]

Here is example of what pulled as a result.

Year/CW	Account	Score	Column1
2019-CW46	AAA	0.00	57.692108
2020-CW03	BBB	0.00	57.692108
2020-CW03	CCC	0.00	57.692108
2020-CW03	CCC	0.00	57.692108
2020-CW03	CCC	13.02	57.692108
2020-CW03	DDD	13.02	57.692108
2020-CW03	DDD	13.02	57.692108
2020-CW03	DDD	13.02	57.692108
2020-CW03	EEE	55.14	57.692108
2020-CW07	EEE	55.14	57.692108
2020-CW07	EEE	55.14	57.692108
2020-CW07	EEE	55.14	57.692108
2020-CW07	EEE	55.14	57.692108
2020-CW08	FFF	0.00	57.692108
2020-CW08	FFF	0.00	57.692108
2020-CW08	FFF	0.00	57.692108
2020-CW08	GGG	57.36	57.692108
2020-CW12	GGG	57.36	57.692108
2020-CW12	GGG	57.36	57.692108
2020-CW12	GGG	57.36	57.692108
2020-CW12	GGG	57.36	57.692108