Well, it’s been a while since I put out a Replay stats with Excel tutorial. Between Illini baseball in full swing and organizing Prairieland 2, it’s been quite busy. Never fear, here is the next segment.
Why a league register?
Up until now, I’ve focused on tracking stats from a team’s perspective. This video will show how I create a league register and we start seeing the big picture. Quite simply, the league register is a dynamic list of every player and their stats. The list is dynamic because total stats are automatically updated every time their game stats are entered.
At this point in the tutorial, a league register is necessary but not always optimal from a design perspective. If you think about it, how often do you look at EVERY league players’ stats when checking out MLB stats? No, we usually look at a team’s stats (like this) or a league leaderboards to give us a snapshot (it’s coming soon).
That said, when a league register is in the form of an Excel table, we do have the capability of sorting, filtering or even better, both. If I want to see who is leading my replay in rbis, a quick sort is in order. I can easily filter by “Chi” to find out what players have played for the Cubs. If I do both, I can see who is leading the Cubs in rbis.
A few of the finer points
Averages are funny. If I sort pitchers by lowest ERA, I will guarantee that someone will have a 0.00 ERA until late summer when every pitcher has a good chance to get some innings in. That’s another chance to use sorting and filtering at the same time. While sorting for lowest ERA, we can use a number filter on innings pitched so that only pitchers with the qualified number of innings will be displayed. The same goes for batting averages and most other averages.
While pivot tables are a lot more flexible (I’ll be getting to them soon), there is a quick and dirty way to copy and paste leader data from register tables. The trick is to copy columns that are not adjacent. To do that, highlight the one column then hold the Ctrl key then highlight the second column. This will allow you to copy this selection so you can paste it elsewhere (see above).
My final note: you’re not limited to the stat categories from your team spreadsheets. Since many stat categories are simply formulas using the core numbers, feel free to add more. There are plenty of ones that can be derived from the stats that were linked.
For example, one that is critical is Plate Appearances (AB+BB+HBP+SH). Plate Appearances are necessary in how I determine who qualifies for average leaders. Just make sure when you add a category, that it is adjacent to the table. By doing that, it becomes part of the table.
Currently, here are categories I’ve added in my 1966 replay:
- Plate appearances
- Extra base hits
- Isolated Average
- Strikeouts/9 Innings Pitched
- Walks/ 9 Innings Pitched
- Hits/ 9 Innings Pitched
- Win %
- WHIP
For those that want to follow along, here are the Excel spreadsheets I’m working with.
