Playing Time Calculation

[Falcane]

Hi everyone - long time lurker, had an old account from years ago but lost name/password and it was tied to my old work email, so apologies for the new account...

I coach a couple elite soccer teams and I am a big nerd and use a lot of statistics and data to supplement my strategies. I'm trying a different, more automated format this year using exports from my iPad's soccer app, and I consider myself a pretty strong Excel user, but one thing has me stumped.

Below is a table I input data into, and a recent game we played indoors with 7 players a side. Columns A (Event) through E (Player In) are automatically imported, the rest is manual. The columns on the right are positions on the field; the numbers represent certain players and I manually swap player numbers in and out when there's a "Substitution" (player not listed comes on for someone) or "Change Position" (two players on swap positions). More often than not, change position + substitution happen roughly the same time because I'm moving people around based on the substitution.

Here's the important part - can anyone think of a way to count total playing time for individual players based on player number? Some kind of SUMIF, or INDEX with OFFSET, I don't know. I'm completely stumped on this one but I'm confident there's a way I can do this.

Thanks for any help.

 

[J.Ty.]

Seems difficult. You do not record who is playing at kickoff. I can also imagine that substitutions happen at halftime, but you do not record them.
The initial team of each half should be somehow inferred from the events, but I can imagine that the goalkeeper is never mentioned, even though he/she is playing all the time.

J.Ty.

 

[J.Ty.]

Forget my previous post, I have discovered where the info about teams is.

Look at this solution (toy size). Teams are in columns B, C, D, A is time (assuming the time is the beginning of the play of the team in this row). Yellow filed is the player, whose playing time is to be calculated.

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G
1	00:00	1	2	3		3	03:00:00
2	01:30	1	2	4
3	03:30	2	3	1
4	04:30	3	2	1
5	05:00
Sheet1

Cell Formulas
Range	Formula
G1	=SUMPRODUCT((B1:D4=F1)*(A2:A5-A1:A4))

 

[Falcane]

Nice, that's pretty good. Only two concerns with that, thankfully neither are really going to prevent me from moving forward with this solution:

1. When I apply the formula to the above data set, everyone's playing time is slightly off, usually by about 1-30 seconds, not any more than that, though. Not really sure why. Trying to dissect the formula in evaluate is pretty difficult.

2. The above data set you'll see a "Game" column - so there will be more than one game. I can manually move the parameters of the SUMPRODUCT to fit each game, but there must be an automated way to do that that I'm missing.

I've uploaded a shell version of the spreadsheet (with player names and team names omitted to protect their identities as they are minors): http://jmp.sh/59qVUSC

You'll see that I have the SUMPRODUCT calculation on the second tab, and it individually adds up game time per game with manual changing of the parameters to fit each individual game.

 

[J.Ty.]

Concerning many games: do you want the tool to sum times from all games, one game at a time, or something else?

J.Ty.

 

[Falcane]

Eventually, both (players get report cards at the end of the year and part of that is transparency to parents over how much playing time they get). But for the sake of this calculation, ideally, a sum of all games. Only reason that table exists on the second tab is I wasn't sure if you could do one big calculation + sum of all games without having to manually add games together, if that makes sense.

 

[J.Ty.]

Method to work with separate games:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H
1	time	game	player1	player2	player3	game	player
2	00:00	1	1	2	3	2	3	02:30:00
3	01:30	1	1	2	4
4	03:30	1	2	3	1
5	04:30	1	3	2	1
6	05:00	1
7	00:00	2	1	2	3
8	01:30	2	1	2	4
9	03:30	2	2	3	1
10	04:30	2	4	2	1
11	05:00	2
Sheet1

Cell Formulas
Range	Formula
H2	=SUMPRODUCT((C2:E10=G2)*(A3:A11-A2:A10)*(B2:B10=F2))

 

[J.Ty.]

Here is a variant summing up all games:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H
1	time	game	player1	player2	player3		player
2	00:00	1	1	2	3		3	04:30:00
3	01:30	1	1	2	4
4	03:30	1	2	3	1
5	04:30	1	3	2	1
6	05:00	1
7	00:00	2	1	2	3
8	01:30	2	1	2	4
9	03:30	2	2	4	1
10	04:30	2	4	2	1
11	05:00	2
Sheet1

Cell Formulas
Range	Formula
H2	=SUMPRODUCT((C2:E10=G2)*(A3:A11-A2:A10)*(B3:B11=B2:B10))

 

[Falcane]

Thank you very much. The second one works like a charm, but the first one (individual games) does not. It returns some negative values, and it only returns the correct value for the first game in the file I uploaded (where there was zero substitutions on/off made) if I change the third SUMPRODUCT parameter to the second row to the final row instead of the first row to the second-last row.

 

[Falcane]

UPDATE: Individual game formula works if you add the "(B2:B10=F2)" parameter as a fourth criteria to the "multi-game" formula.

Thanks for your help!!!!