The Robservatory

Robservations on everything…

 

An even more improved run-tracking Excel workbook

2020 update: Everything here is out of date now, and has been replaced with my post on the 2020 version of the worksheet. In there you'll find a download link and full instructions. I'm leaving this article up only because it may be linked to from other places.

 

2019 update: I've uploaded new files (in one zip archive this time) with a few changes and fixes. These files are also set up as "master" files: The idea is you duplicate one, rename it for the current year, then use it. When the next year rolls around, repeat the process. This way, you don't have to use the macro-enabled version to delete data at each new year. Download the new files.

About two years ago, I created a basic-but-functional run tracking workbook (created in Excel). It worked well, and helped me through my 2,016 mile year in 2016. I didn't run nearly as much in 2017 (on purpose), but 2018 is upon us, and I'm going to up my mileage this year—probably not to 2,018, though!

In preparing this year's version of the workbook, I addressed a few things that bugged me about the first one: It was ugly, changing years was difficult, and it was ugly. It was also really ugly. Did I mention it was ugly? Anyway, here's what I've changed with the new version:

  • Years are now easily handled; just input the year you wish to track, and the workbook does the rest, including leap years.
  • All run data can be deleted with one button click—and yes, there's a confirmation first. (Requires macro version of workbook.)
  • The pace calculator is no longer a separate worksheet; it's integrated into the Overall worksheet.
  • It's not nearly as ugly as it was before—layout is improved, gridlines are gone, tables are cleaned up, etc.

As noted, there are two versions of the workbook—one contains a macro that can erase the run data from each monthly worksheet, the other does not contain that macro. This is something you'll only do once a year, but it's much easier with the macro version.

To begin, download the zip archive of both files. Expand the archive, and duplicate the macro or non-macro version (as you wish; the macro version needs a newer version of Excel) for the current year. When the next year rolls around, repeat the process.

The instructions haven't changed much from the original post, so refer to that for assistance if needed. Here's what's different:

Input year: The workbook will now handle any year, so there's a cell to fill with the year you'd like to track. Input this as a number, not as text. Once input, all formulas will adjust, including adding a day in February for leap year, if necessary. Also, I've set it up 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. I need to test this more, though, so there may be updates forthcoming.

Clear run data: If you have the macro version of the workbook, at the bottom of the Overall worksheet you'll see the big bold ugly button at right. This will do exactly what it says: Delete all your run data. It will warn you first, and give you a chance to cancel. But be warned, there is no undo if you run this and then want your data back!

While dangerous, this button makes it very easy to set up for a new year of running: Copy the current year's workbook, rename the copy for next year, open it, change the input year, then click the big red button.

Pace calculator: As noted, it's been integrated into the Overall worksheet, near the top. Fill in the blue-shaded cells, and you'll get your pace in both minutes per mile and miles per hour. Sorry, no metric support.

Note that there are some behind-the-scenes calculations (some quite ugly) that need to be done to make this work. Those calculations—and others—reside in columns M and N, which are currently set to zero width (but not hidden via the menus). If you delete those columns, pretty much everything will stop working. So don't do that.

Location: As before, I check only for the word "outside" in this column on each month's worksheet. If it's not there, the run is assumed to be inside, as I do a lot of treadmill running.

Shoes: You can track up to three pair of shoes (because that's all I use and had room for in the worksheets). Just give each one a name on the Overall chart, and they'll carry over to the monthly worksheets, and you'll be able to track miles run per shoe.

That's about it…I'm pretty sure I'll have to fix a few things as the year progresses, given how much I changed. But I like this version much more than I did the first.

24 Comments

Add a Comment
    1. You may want to download the updated files - there are a couple bug and layout fixes.

      -rob.

    1. I think all you need to do is to change "Miles" to "KMs" wherever you see it—the calculations for pace don't care what units they're working in, and everything else is just a number. So it should work just fine to enter KMs everywhere; changing the labels will just make it also look right.

      I won't be doing this, though, as I don't have the time and don't need the KM version myself.

      -rob.

    1. It's a manual thing; just increment each time you need one or more. I've thought about having a way to code them into the worksheet itself, and maybe I'll do that for next year.

      -rob.

  1. Are you doing an update for 2020? I've been using this for the past couple of years and I'm addicted! Thanks!

  2. Hey Rob, absolutely LOVE this tool! Thanks for sharing it!
    I'm using it in Numbers on a Mac, all seems to work except from the Chart tab, that doesn't seem to have updated with my target and mileage so far this year... appreciate it may just be that it won't work because of differences between Excel and Numbers, but wondering if you had any idea what I may be able to do to get that working?
    Cheers
    Tom

    1. Tom:

      The problem is that the chart relies on the INDIRECT formula, which gets the value referenced by the contents of another cell. And while Numbers has INDIRECT support, its format for cell references is totally different than that of Excel.

      The good news is this is fixable, the bad news is you’ll have to do it yourself :) … I’m not a Numbers user, and I really don’t want to get into having to support two formats of the workbook. So here’s what you need to do—I think, based on some simple testing here.

      Open the workbook and go to the chart_data worksheet. In cell F4, paste this formula:

      IF(ISBLANK(INDIRECT(Q4)),NA(),INDIRECT(Q4))

      Copy it to the end of the F column. This is actually not changed at all from Excel, but it was probably removed when you opened the workbook (converted to values, technically.)

      Next, on that same worksheet, go to column Q, and paste this formula in cell Q4:

      VLOOKUP(MONTH(B4),$N$4:$O$15,2,1)&”::Table 1::D”&DAY(B4)+9

      Copy that down to the end of column Q … and with any luck, when you switch to the chart, you’ll find it’s working.

      -rob.

  3. Hey Rob, for some reason the chart is not updating. There seems to be an error in the F column formulas in the chart_data tab.

    1. Having now looked into it, this is fixable, though I have to make an assumption: If a “run distance” cell is empty, and it’s in a cell that’s for a day earlier than today, then I will assume there was no run and enter a ‘0’ value for charting purposes. This should work fine, because if you go back and enter a run you forgot, for example, then the actual value will get used and the chart will reflect that.

      The formula for this now looks really messy…

      =IF(ISBLANK(INDIRECT(Q64)),IF(B64<TODAY(),0,NA()),INDIRECT(Q64))

      …but it works :).

      I’ll post a new article with a link to the updated spreadsheet at some point in the near future.

      -rob.

  4. I see why now. You have to start from January. If you don't start in January, the chart doesn't show anything even if you have runs logged in later months.

  5. Putting a zero in on non run days fixed it, thanks! Loving this spreadsheet so much, I like to set myself a yearly mileage target, and then find motivation in seeing how much I can beat the target by, so this tool is perfect, thanks again!

Leave a Reply to Bill Steele Cancel reply

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

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