Home Articles posted by Thomas Nelshoppen (Page 63)

Author: Thomas Nelshoppen

I am an IT consultant by day and an APBA media mogul by night. My passions are baseball (specifically Illini baseball), photography and of course, APBA. I have been fortunate to be part of the basic game Illowa APBA League since 1980 as well as a frequent participant of the Chicagoland APBA Tournament. I am slogging through a 1966 NL replay and hope to finish before I die.

Keeping APBA Replay Stats with Excel video: Creating leaderboards using PivotTables

pivot

I may not have a voice for radio like TWIA’s John Asalon but I still got a little information to disseminate. This episode of “Keeping my APBA Stats with Excel” goes out to Brent Cline.  He’s been asking about PivotTables and quite honestly, they are pretty cool.

In short, PivotTables allow you to draw relevant data from an Excel table and display it in a meaningful fashion.  They are perfect to use to dynamically update stat leaderboards. Assuming you already have a league registry like I demonstrated in this episode, PivotTables can be very versatile.

They can also be a pain in the neck to set up sometimes. PivotTables with simple stats are not too difficult.  However, for averages when you want to filter out players who don’t have the required amount of plate appearances to qualify, it is definitely a multi-step process.

[make sure to watch at full screen]

I also want to emphasize to everyone to keep your Excel tables defined and separate from others.  It’s important to make sure all rows and columns are adjacent.  In addition, make no unrelated data borders your table.  If you follow these rules, you’re golden.

I only inserted a few PivotTables in the above demo.  To get an idea of what my current setup looks like, this is what my Hitters Leaderboard looks like for my 1966 NL season.

Finally, for those who want to follow along, here are the files I’ve been working with for my demo.

thanks, Brent!

 

Replay stats with Excel: creating a standings table

Hey APBA replayers who use Excel, I have another Replay Stats with Excel video up on YouTube. As always this is using my 1966 NL replay.

 

 

[be best if you watch in full screen mode to see all the Excel goodness]

This episode deals with creating a league standings table.  As with most everything in my Excel setup, it is pretty much self-sustaining once you create it.  The biggest concept used is linking cells as you are simply pasting links from each team’s total pitching wins and pitching losses.

It isn’t too difficult to create and once you do, you can format to your heart’s delight to make it look nice.  The trickiest part for me was the Games Back formula.  I have found one that has worked pretty well.  Here it is broken down…

Assuming the first place team is on row 4 and the Wins column is C and the Losses column is D, this formula should be entered for the first place team:

=(($C$4-C4)+(D4-$D$4))/2

Since the first wins cell and the last losses are absolute references, you should be able to copy and paste that formula (or simply click and drag it down as in the video) and the relative cells references will change and the absolute ones will not.

Files used in this video

Here are the Excel files I was using during the video.  They are compressed in .zip format.

Mentioned in this video

iScore

This Week in APBA by John Asalon

Coming up next: Pivot tables!  Also coming up:  Game schedules and results.

If you want to watch past replay Excel tutorial videos in this series, here is the link.  I hope this helps!