A while back after an extra-inning game in my 1966 NL replay, I was wondering just how well each of my teams fared in those games. It occurred to me that with a little work and some Excel expertise, I could come up with a table that would display team splits.
My vision of this would include win-loss records for extra-inning games, one-run games, shutouts, home/away as well as time-based records such as first half/second half and monthly records. I have all the raw data already. It was just a matter of getting it filtered correctly so that the numbers are there.
I feel I spend most of my time figuring out how to exactly accomplish each task. Once I get the process done for one team, it’s mostly just a matter of copying the formulas to the other teams’ sheets and maybe changing a few variables.
From the beginning, I have used a master schedule and each team has its own schedule which links from that schedule. I used the teams’ schedules to calculate the splits data. I would create columns that would help determine answers for each game…
– Is the team home or away?
– Was it an extra-inning game?
– Was it a one-run game?
– and did the team win or lose?
…and so on. There were a lot of ‘if’ formulas involved.
From there, I would total the games using the ‘sum’ function to total the games.
Hiccups along the way
There were a couple hiccups. After I thought I was done, I realized I actually forgot to include extra-inning games. That’s a bit funny because that is what prompted this project in the first place.
This was a little frustrating as I had already rolled out the formulas to the other teams and had re-visit each of the ten teams to fix the issue.
This project broke a couple things. The biggest issue was that my current win/loss streak total for each team was reporting the wrong number, in some cases a 0. In the process of everything, the cell holding the streak number was moved so it pointing to the wrong cell.
It took a while to figure out was wrong but once I did, it was a matter of time before it was all fixed again.
All good now
Right now, as long as I don’t mess with it, the dashboard is good. All stats are up-to-date and correct. This is a dynamic system so like the leaderboards, the team splits will also be updated as I enter in new games.
At some point, I’ll dive deeper into the technical side if people find this interesting.
Will I be implementing player splits? Maybe next replay!
