Why is my Weighted Average Formula Not Working?

[rob51852]

Hi,

I am trying to come up with a weighted average formula but keep getting myself tied up in knots...

I have a table called ScorecardBattingTable and here is a small excerpt:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	battingTeam	inningsNo	batsmanId	howDismissed	bowler	runs	ballsFaced	_4s	_6s	convDate	League	Season	venue	GrBatAveAdj	GrSRAdj	GrBPercentAdj	LgBatAveAdj	LgSRAdj	LgBPercentAdj	GrAdjRuns	GrAdjSRRpB
2	Lahore Qalandars	1	56194	caught	259551	30	20	5	0	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	28.2	1.41
3	Lahore Qalandars	1	512191	caught	251721	46	36	4	2	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	43.24	1.201111111
4	Lahore Qalandars	1	532424	caught	1159371	5	4	1	0	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	4.7	1.175
5	Lahore Qalandars	1	41434	caught	42639	19	21	2	0	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	17.86	0.85047619
6	Lahore Qalandars	1	388678	bowled	42639	3	3	0	0	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	2.82	0.94
7	Lahore Qalandars	1	18632	caught	43265	26	16	4	0	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	24.44	1.5275
8	Lahore Qalandars	1	221140	not out		48	21	5	3	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	45.12	2.148571429
9	Lahore Qalandars	1	1218226	not out		0	0	0	0	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	0
10	Multan Sultans	2	520183	caught	1161606	12	9	1	1	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	11.28	1.253333333
11	Multan Sultans	2	251721	caught	221140	50	29	4	3	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	47	1.620689655
12	Multan Sultans	2	233901	caught	1206623	27	22	1	0	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	25.38	1.153636364
13	Multan Sultans	2	318845	caught	1161606	18	18	1	0	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	16.92	0.94
14	Multan Sultans	2	10582	caught	221140	1	2	0	0	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	0.94	0.47
15	Multan Sultans	2	716733	caught	Shah	30	19	2	2	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	28.2	1.484210526
16	Multan Sultans	2	42639	bowled	1161606	0	1	0	0	15/11/2020	Pakistan Super League	2020/21	National Stadium, Karachi	0.94	0.94	0.93	0.952380952	0.983483702	0.897503983	0	0
ScorecardBatting

And I am trying to produce a weighted average by batsmanId (F3) and League (A285). The range I want to average is GrAdjSRRpB and the weightings are in ballsFaced.

This is the latest attempt:

=SUMPRODUCT((ScorecardBattingTable[batsmanId]=$F$3)*(ScorecardBattingTable[League]=A285),ScorecardBattingTable[GrAdjSRRpB],ScorecardBattingTable[ballsFaced])/SUMIFS(ScorecardBattingTable[GrAdjSRRpB],ScorecardBattingTable[batsmanId],$F$3,ScorecardBattingTable[League],A285)

Can anyone see what the issue is?

Thanks

 

[maabadi]

I don't think A285 is address of your Criteria cell and it is league name. Then you forgot to add "" to your text for finding. change formula to this:

=SUMPRODUCT((ScorecardBattingTable[batsmanId]=$F$3)*(ScorecardBattingTable[League]="A285"),ScorecardBattingTable[GrAdjSRRpB],ScorecardBattingTable[ballsFaced])/SUMIFS(ScorecardBattingTable[GrAdjSRRpB],ScorecardBattingTable[batsmanId],$F$3,ScorecardBattingTable[League],"A285")

 

[rob51852]

Thanks Maabadi. This gives me a #Div/0! error unfortunately.

 

[maabadi]

Because you don't have league name equal "A285" in your League Column Data or your F3 cell (criteria1) is Empty. with your uploaded data, I test with "Pakistan Super League" and it worked.