The Robservatory

Robservations on everything…

 

The 2020 edition of my Excel run tracking workbook

At the end of 2016, I first posted about my run tracking Excel workbook. That first version was crude, but functional, and I used it to track every mile of my "2,016 miles in 2016" running goal. I posted a minor revision for 2017, then made some major updates for 2018. When 2019 rolled around, I made a few more changes, which I released by way of a note added to the 2018 post.

Now that 2020 is here—I know, it's a bit past January, sorry!—I've made yet more changes, and have decided it's time to replace both of the previous posts with one new all-in-one post. Here you'll find a link to the latest version of the workbook, as well as full instructions on how to use it.

Apr 15 2020 Update: There's an error on the Overall worksheet, in cell F49. I've fixed it and replaced the download links in this post. You can either download the new archive and transfer your data…or you can just make the change. Cell F49 on Overall should contain this formula:

=SUMIF(Jan!H10:H40,"outside",Jan!D10:D40)+SUMIF(Feb!H10:H37,"outside",Feb!D10:D38)+SUMIF(Mar!H10:H40,"outside",Mar!D10:D40)+SUMIF(Apr!H10:H39,"outside",Apr!D10:D39)+SUMIF(May!H10:H40,"outside",May!D10:D40)+SUMIF(Jun!H10:H39,"outside",Jun!D10:D39)+SUMIF(Jul!H10:H40,"outside",Jul!D10:D40)+SUMIF(Aug!H10:H40,"outside",Aug!D10:D40)+SUMIF(Sep!H10:H39,"outside",Sep!D10:D39)+SUMIF(Oct!H10:H40,"outside",Oct!D10:D40)+SUMIF(Nov!H10:H39,"outside",Nov!D10:D39)+SUMIF(Dec!H10:H40,"outside",Dec!D10:D40)

If you already know what you're doing with my workbook and just want the newest version, grab it here. If you're curious, here's what's new in this version:

  • There's no more macro-enabled version, which was used to quickly delete all entries. Instead, keep the original file intact, and just duplicate the master each year for a new year's tracking. This is much simpler (and safer) than the prior macro-based way to start a new year.
  • I worked around an issue where not entering a run distance—even if it was zero—for each day of the year would break the progress chart.
  • Fixed a few layout issues (like "0" appearing for a shoe name if you didn't enter three pairs of shoes), revised a few formulas, and just generally tried to clean things up.

Note: This workbook will probably not function correctly in Numbers without some modifications—see my note to "Tom" in the 2018 version's blog post for the details. I have not tested that fix with this version of the workbook, however, so you're pretty much on your own if you're using Numbers.

If you've never used my run tracker and would like to know more about it, keep reading…

As a start, here's a portion of the Overall sheet of the workbook:

In addition to the overview, there are worksheets for each month, as well as a chart that tracks your progress to date against your full-year goal. It's quite easy to use, and for me, I like being able to see daily progress against my running goal.

To start using my workbook, first you need to download it and then expand the compressed file. After expansion, keep the original file somewhere safe and unmodified. Now create a duplicate of that file, and use that duplicate for the current year's run tracking.

When you open the workbook, it should be on the Overall worksheet; if not, click that worksheet in the list at the bottom of the window. There are a total of 15 worksheets in the workbook; here's a summary of each:

  • Overall: Here's where you input the year you're tracking, your mileage goal, your shoe information, and your weight. You can also calculate pace if you know a distance and duration, and enter any hurt/sick or travel days. Down towards the bottom are some summary statistics on the lengths of your runs, and how many miles you've put on each of your shoes.
  • Chart: The chart displays your YTD progress against your goal. The goal is the diagonal line, which is simply plotting the required number of miles per day. As you run, your distances will start filling the chart, hopefully tracking along or above the goal line.
  • Jan through Dec: These are the sheets where you enter your actual run information.
  • chart_data: This is where the ugly work required to make the chart happens. Don't touch this sheet.

Here's how to use each of these worksheets to track your running…

Overall

The first thing you need to do is enter some things the workbook needs to know…

Year to track: Enter the year that you'd like to track with the workbook. It is critical that you input this as a number (just type in the actual year), not as text. Once there's a value here, all formulas will adjust, including adding a day in February for leap year, if necessary.

I've tried to set up all the formulas so that your data shouldn't change (i.e. "days left" stops counting) once the year has passed, so you should be able to just archive the year's workbook as is, without fear that things will change over time. If you spot a cell that's not working properly when the year changes, please let me know.

Goal: How many miles you want to run in the defined year. Note that the workbook is not really set up for kilometers, but they're just numbers—in theory, if you enter everything in kilometers and switch any labels around, things should "just work," but I haven't tested this.

Shoes: You can track mileage on up to three different pair of shoes. If you have more than that, well, you're out of luck :). If you have only one or two pairs, delete the unneeded entries (in the blue area only).

Aside: The Pace Calculator

The Pace Calculator lives to the right of the variable inputs, and it's used to convert time-and-distance values into both miles per hour and minutes per mile pace info—the miles per hour value is what's used on each month's run tracking worksheet. Just enter your distance and time (in hours, minutes, and seconds), and it will spit out the results.

Note: The calculator relies on some formulas that are hidden in column M, which is set to zero width. Do not delete the column, or modify any of its formulas (some are used elsewhere, too).

Immediately below the inputs and calculator is an overview of the full year, which shows your year-to-date progress along with a month-by-month mileage and weight tracker. Here's what it looks like, using some fictitious data against a full-year goal of 900 miles (click to see the full image):

The Progress as of section shows you exactly what you've done against your goal—days left to go, and your progress against the year-to-date goal—as of today's date. Below that, a table shows each month's goal, and your progress (current month) or results (past months) against that goal. There's also space on the right to enter your weight twice a month, if you're interested in tracking that, too.

Finally, below the table is a full-year outlooking, showing you how many miles are left to the goal, how many days are left to get there, then comparing the average miles per day required to meet the goal versus your actual year to date average miles per day. The final value here is the adjusted required miles per day to exactly meet your goal—if you're currently ahead of goal, this will be lower than your target miles per day, and if you're behind on your goal, this figure will be higher than your target miles per day.

The bottom section of the sheet contains some summary statistics, plus two rows where you can input data (again, click for the large version):

If you enter values in the blue cells, those sick/hurt and travel days will be excluded from any of the "per day" calculations. Below that are a number of statistics for days you ran out of possible running days, total miles, inside vs. outside miles, and then a chart showing runs by distance—I used kilometers for this chart, as I like running 10Ks, so it made sense to me. Finally, the total distance you ran on each pair of shoes is shown—this is useful knowledge as the general advice is to replace shoes somewhere between 300 and 500 miles.

Chart

Not much to say about this worksheet; it displays a year-to-date chart of your progress against your goals:

The box at the top shows year-to-date totals, the diagonal line is a running total of your daily mileage goal, and the blue filled-in section are your daily runs.

There is no user-changeable data on this worksheet.

Jan through Dec

These worksheets are where you'll enter your daily run information:

At the top, the Progress, Results, and Shoes boxes do not contain any user-modifiable data—they simply summarize the month's running activity by progress against goals, days run (and 10K+ days), and miles put on each of your defined shoes.

Below that, Please do not modify the Date or Day columns. If you do, pretty much everything will stop working. Enter each day's mileage in the Miles column, then any other data you'd like to track in the associated columns. With the exception of the Location (outside will mark a run as outside, everything else is assumed indoors) and Shoes (text entered must match your defined shoes) columns, nothing else affects any calculations or is displayed elsewhere.

At the start of the year, the worksheets are arranged in month-order tabs at the bottom of the workbook window:

As months go by, I drag-and-drop completed months to the end of the list (and change their color by right-clicking on the tab and using the Tab Color menu item), so that the current month is always close to the left end of the workbook:

You do not have to enter a Miles value for each day in the month. Any blank Miles cells that are prior to the current date will be assumed to be zero. If you go back later and fill in values, everything will adjust as expected.

chart_data

There's nothing you can do on this worksheet except break the chart, so please, don't change anything :). There are a lot of really ugly formulas to make everything on the chart work right, and this is where they live.

Caveats and cautions

I originally created this workbook for my own use, and although it seems to work well and correctly for me, no warranty is expressed or implied. There may be formula errors (though I've fixed many that I've caught over the years). This spreadsheet will not help you physically get out and run—that’s all on you! But it may help you stay motivated to run—for me, looking at the chart each day is a big motivator, as I always want to keep the chart's "blue mountain" above its sloping gray line!

If you have any questions, please post them or contact me, and I’ll do my best to answer them.

8 Comments

Add a Comment
  1. hi, this is a great excel...i like it very much.
    Was looking into the excel if it was possible to shift somehow the starting date.
    we all know people dont start in Jan and would be good to shift the graph and data to monitor

      1. Funny, I just found that one the other day; about to upload an updated version.

        thanks;
        -rob.

    1. That would be ... very complicated. I might look into it later this year, in getting ready for next year, but it will take reworking the graph quite a bit, and adding a new start date field.

      -rob.

  2. Been looking to do something like this for a while. Love this, much better than me trying to figure it out! However, I do have one question: why do you put distance in miles in some places and km in other? That's the only slight criticism I have, having everything in miles would be awesome! Also I wish I could do pace based off of per mile instead of mph, but once again I love the layout and ease of it!

    1. Dylan:

      The only section that's in KM is the summary on the main page, and that's because I was focused on 10Ks when I originally made the worksheet. It'd be pretty easy to change it to miles - if you expand columns L through P, you'll see that column M holds the ranges for the table to the left. Change those to whatever you'd like in miles (you'll have to change the text labels in column B as well, as they're just typed.

      Next, the forumulas in D54 to D63 reference column J on the chart_data worksheet; change those to all reference column F instead, and you'll be working in miles.

      As for the pace, that data doesn't go anywhere at all—it's just there for reference. So change the header on each month's tab to mins/mile or whatever, and you're done, because whatever you put here won't go elsewhere. One reason I like MPH, though, is that Excel will want to convert "7:13" into a time, so you'll have to enter it as a text ('7:15) to make that not happen.

      -rob.

Leave a Reply

Your email address will not be published. Required fields are marked *

The Robservatory © 2020 • Privacy Policy Built from the Frontier theme