Calculate average of a column for dates only if they don't contain another value

[slaytanic]

Hi, what my confusing title is trying to ask is this: I want to calculate average points for a player when another player isn't playing. So in this list:

DATE PLAYER PTS
1-1-2017 Player1 10
2-1-2017 Player2 15
2-1-2017 Player1 18

What would be the formula to calculate avg points by player1 when player2 wasn't playing? Example is easy, it should be 10, but the original file has thousand of inputs - players, dates etc.

thank you

 

[StephenCrump]

Welcome to the Forum!

Perhaps like this:

D16: =IFERROR(SUMPRODUCT(C$2:C$11,--(B$2:B$11=B16),--ISERROR(MATCH(A$2:A$11,A$2:A$11*(B$2:B$11=C16),)))/SUMPRODUCT(--(B$2:B$11=B16),--ISERROR(MATCH(A$2:A$11,A$2:A$11*(B$2:B$11=C16),))),"n/a")

D1:I11 - shown just to illustrate the calculations

Excel 2010
	A	B	C	D	E	F	G	H	I
1	DATE	PLAYER	PTS	1	2	3	4	5	6
2	1 Jan 2017	Player1	10	10	10
3	2 Jan 2017	Player2	15				15
4	2 Jan 2017	Player1	18		18
5	3 Jan 2017	Player1	13	13	13
6	10 Jan 2017	Player1	11	11
7	10 Jan 2017	Player3	11						11
8	11 Jan 2017	Player3	12					12	12
9	12 Jan 2017	Player1	20	20	20
10	13 Jan 2017	Player2	16			16
11	13 Jan 2017	Player3	17					17
12
13			Average	13.50	15.25	16.00	15.00	14.50	11.50
14
15	Scenario	Player	Not player	Average
16	1	Player1	Player2	13.50
17	2	Player1	Player3	15.25
18	3	Player2	Player1	16.00
19	4	Player2	Player3	15.00
20	5	Player3	Player1	14.50
21	6	Player3	Player2	11.50
1

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td]DATE[/td][td]PLAYER[/td][td]PTS[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

1/1/2017​

[/td][td]Player1[/td][td]

10​

[/td][td][/td][td][/td][td]player1[/td][td]

10​

[/td][/tr]
[tr][td]

3​

[/td][td]

2/1/2017​

[/td][td]Player2[/td][td]

15​

[/td][td][/td][td][/td][td]player2[/td][td]n/a[/td][/tr]
[tr][td]

4​

[/td][td]

2/1/2017​

[/td][td]Player1[/td][td]

18​

[/td][td][/td][td][/td][td]player3[/td][td]

25​

[/td][/tr]
[tr][td]

5​

[/td][td]

3/1/2017​

[/td][td]Player3[/td][td]

25​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

4/1/2017​

[/td][td]Player3[/td][td]

45​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

4/1/2017​

[/td][td]Player1[/td][td]

15​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In G2 control+shift+enter, not just enter, and copy down:

=IFERROR(AVERAGE(IF(ISNA(MATCH(IF($B$2:$B$10=$F2,$A$2:$A$10),IF(1-($B$2:$B$10=$F2),$A$2:$A$10),0)),$C$2:$C$10)),"n/a")

 

[Peter_SSs]

Welcome to the MrExcel board!

For the same sample data as Stephen, you could also try this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

I'm not sure how well either suggestion will go with thousands of rows, but give them a try.

Book1
	A	B	C	D
1	DATE	PLAYER	PTS
2	1-Jan-17	Player1	10
3	2-Jan-17	Player2	15
4	2-Jan-17	Player1	18
5	3-Jan-17	Player1	13
6	10-Jan-17	Player1	11
7	10-Jan-17	Player3	11
8	11-Jan-17	Player3	12
9	12-Jan-17	Player1	20
10	13-Jan-17	Player2	16
11	13-Jan-17	Player3	17
12
13
14
15	Scenario	Player	Not player	Average
16	1	Player1	Player2	13.5
17	2	Player1	Player3	15.25
18	3	Player2	Player1	16
19	4	Player2	Player3	15
20	5	Player3	Player1	14.5
21	6	Player3	Player2	11.5
Average

Cell Formulas
Range	Formula
D16	{=AVERAGE(IF(B$2:B$11=B16,IF(COUNTIFS(A$2:A$11,A$2:A$11,B$2:B$11,C16)=0,C$2:C$11)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[slaytanic]

Thanks guys, it works! I didn't think something so messed up could be calculated but I guess everything can with a little logic!

cheers

 

[Peter_SSs]

Thanks guys, it works!

I have no idea which solution worked best for you but I am glad at least one of them did.