My super fit cousin Joe Perry contacted me with a few questions about an Excel spreadsheet he uses to track his running.
More About Joe
When Joe isn’t running Internet Kent (his ISP) he’s often participating in Tough Mudder competitions throughout the U.S. and Canada. He has completed many Tough Mudders and he’s also an ambassador. As you can see above, Joe is known for his many painted faces.
To keep in shape he has an incredible workout routine. Running is a big part of that. How does Joe know if he’s on pace for a good year of running? That’s where I came in handy.
Joe enters his monthly data in a spreadsheet. How can Joe…
- easily sum his Year-to-Date totals?
- compare to previous years?
- visualize his running stats?
Joe doesn’t want to get nerdy with the data. He just wants to enter a few numbers at the end of the month and quickly study the results. That’s ok Joe. I’ll do the nerdy stuff while you play in the mud.
Joe’s Running Template
Download Joe’s Running Tracker. Download a blank Running Tracker.
Set-up A couple of simple selections to set it up.
Instructions Screen shot indicates input cells, formula cells and conditional formatting.
Running Tracker Enter data in this sheet. Formulas calculate stats. Conditional formatting highlights the data. Now you can get in shape like Joe and participate in Tough Mudders!
Other Sheets I created sheets ‘Chart’, ‘YTD Stats’, ‘YTD Chart’ to help Joe visualize and study his data.
Examining Joe’s Stats
Joe has been tracking his running stats since 2013. What do his running stats tell us?
Joe’s monthly running totals.
Dark green highlights top months across all years.
Dark orange highlights lowest months.
Here we see monthly km averages.
Joe runs more in the nice weather.
Lots of stats here! Yearly Totals is obvious. Rank Yearly Totals is also clear.
Km needed to reach 1st, 2nd, 3rd Important numbers for Joe! It looks like Joe’s 2018 totals have a chance of pulling into 3rd place! Run joe run there’s only a few days until the new year!
Total Runs Joe enters these numbers and then it’s easy to calculate Average Km per run.
At the bottom Joe can study is Year to Date totals! Totals YTD Jan to Nov allows Joe to quickly compare this year to previous years at the same point in time. Rank Totals YTD Jan to Nov ranks based on the same number of completed months.
I also created three additional sheets with charts and Year to Date stats for Joe.
When Joe is finally done running for the year he can curl up with a healthy smoothie and enter his December running total in Excel!
Year End Update!
Joe ran 208.15 km in December! Enough to reach 3rd place! Congrats Joe! Here is the updated Excel file (2018 completed).
My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada.
While Joe excels at running I just Excel 🙂
Microsoft Excel is my favorite data tool.
Pingback: Why and How to Unpivot Data | My Spreadsheet Lab