Running logs are on my list of topics I am planning to write about. Whether you use a paper log, an electronic version, or an online source, there are lots of options. Until I have an opportunity to write that post, I thought I would put together some basic and useful math formulas. These can be used for your running logs or while doing some calculation. Later in this post I’ll show you how to use some of this math in your spreadsheets.
The information in this post is very easy to follow but I am assuming nothing and will start out very simply (explaining everything) and bring everyone up to the same level where I discuss the spreadsheet formulas. If you are just looking for the formulas, jump down a little bit past the explanations. This post is sort of an “all in one place” cheat sheet.
Time / Distance / Pace
These are probably the most basic, and important pieces of information that we runners record. Whether it is to measure progress or to take a snapshot of where we are, every runner wants to know how fast they ran (pace), how far they ran (distance), and how long did it take them (time or duration).
If we have any two of these values, finding the third is no more difficult than using the corresponding forms of the following formula (these three are the same formula but in different forms):
Let’s begin by making sure we understanding what these formulas mean, how to use them, and finally how to implement them in our spreadsheets (ex. Excel or Google Sheets).
In all the forms of the above formula, Time is in
hours : minutes : seconds, Distance is in
kilometers, and Pace is in either
minutes/kilometer matching the unit Distance is in.
For the remainder of this post I am going to use Miles as the unit to use for Distance, but you can just as easily use Kilometers instead and all the formulas will remain the same – only the units will change:
- If Distance is in Miles, then the Pace unit will be Minutes per Mile (min/mile)
- If Distance is in Kilometers, then the Pace unit will be Minutes per Kilometer (min/km)
Your pace is of course how long it takes you to run 1 mile (or 1 kilometer). For example, if you run 2 miles in 18:30 (18 minutes and 30 seconds) your pace is 9:15.
Note that if you are trying to figure this out using a calculator, then you need to convert the pace into a decimal number: 18:30 is 18.5 minutes (18 minutes + 30/60 seconds = 18.5 minutes). When I show you how to enter this into a spreadsheet this step will be taken care of for you.
If you run for a particular amount of time at a set pace, then you can figure out your distance. For example, if your pace is 8:15 and you run for 1 hour, then using the formula it can be determine you will run 7.27 miles.
As noted earlier, if you are trying to do the calculations using a calculator you convert the pace into a decimal number (8:15 = 8 + 15/60 = 8.25 minutes) to represent minutes and the 1 hour equals 60 minutes. This again is taken care of when using a spreadsheet.
The third form of the formula determines time (the duration) of your run. If you run for a particular distance at a particular pace, then you can calculate how long you will run for.
For example, if you run a distance of 3.1 mile at a pace of 7:45 then using the formula you know that you will run for a time of 24:02.
Once again note that if you are using a calculator you would convert the 7:45 into the decimal 7.75, and the result 24.025 can be converted back to standard time notation doing the opposite:
24.025 = 24 minutes + (0.025 * 60) seconds = 24:02
Using the Formulas in a Spreadsheet
Using any of the formulas above is not too difficult but it may be error prone when converting between the various formats of Time when doing it by hand or with a calculator. Luckily when using a spreadsheet application like Microsoft Excel or the free Google Sheets most of the work is done for you.
Below is a screen shot of a simple spreadsheet I created as an example that has all three forms of the formula we discussed previously. As you can see, I have the left box with 2 known values in a row, and the unknown field in the right box on that row.
To set up the above spreadsheet:
- Columns B, D, F, and H should be formatted as Custom
- Columns C and G should be formatted as Number (or Custom
- To calculate Pace, enter in cell H4 the formula:
- To calculate Distance, enter in cell G5 the formula:
- To calculate Time, enter in cell F6 the formula:
Two Additional Common Calculations Used By Runners
Beyond the formulas used to determine your Pace, Time, and Distance, or the formulas to convert between time and distance units, runners may also use formulas to determine their BMI (Body Mass Index) and their Training Heart Rate Zones.
Many medical professionals, athletes, and health conscious individuals use these calculations. I will use another blog post to discuss how and why to use these. So without any commentary, here are the formulas:
BMI – Body Mass Index
Training Heart Rate Zones
There are a few ways of defining and determining your heart rate zones. To further add to the confusion, the heart rate values in these zones are not equal. To determine which is best for you, head over to Google or Bing and do a little research. Basically it comes down to two methods:
- Percent of Maximum Heart Rate
- Percent of Maximum Heart Rate Reserve (Karvonen Method)
For the the first method it only requires your maximum heart rate (MHR). For the second method, take your maximum heart rate (MHR) and subtract your resting heart rates (RestHR) to come up with your Heart Rate Reserve (HRR). Formula:
HRR = MHR - RestHr
There are many methods to calculate your MHR, the most basic being
220 - your_age. For some this may not be an accurate enough value so you may want to Google additional methods such as Dr. Martha Gulati et al, Londeree and Moeschberger, Miller et al, and Bell State University to name a few.
To obtaining your RestHR, count the number of heart beats in one minute when your heart is at rest such as first thing in the morning.
Depending on which method you decide to use, you will calculate theTarget Heart Rates for each zone using one of these formulas:
MHR * % = TargetHR
(HRR * %) + RHR = TargetHR
|Type||% to use for TargetHR||Zone|
|Very Light||50 – 60%||Zone 1|
|Light||60 – 70%||Zone 2|
|Moderate||70 – 80%||Zone 3|
|Hard||80 – 90%||Zone 4|
|Maximum||90 – 100%||Zone 5|
In case my explanations have confused you, lets do an example using method 2 (Karvonen Method):
Person’s Age: 40
Person’s RestHr: 60
Want: Zone 2 heart rate range
MHR = 220 - age = 220 - 40 = 180 HRR = 180 - RestHR = 180 - 60 = 120
Zone 2 is between 60 and 70%:
(120 * 60%) + RestHR = (120 * 0.6) + 60 = 132 (120 * 70%) + RestHR = (120 * 0.7) + 60 = 144
So for this person that range for zone 2 would be a heart rate between 132 – 144 beats per minute.