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