A Minutes-Per-Mile Formula in LibreOffice Calc

Like most runners, and especially geek runners, I’m big into tracking my runs. My main measurements are distance, time and minutes-per-mile. I know there are apps that will do this for you. I used one for a while, but I decided to go back to doing it the old fashioned way: record it in a spreadsheet.

Now I’m a Linux Guy, so we’re going to be talking about LibreOffice Calc. What we’ll be looking at may work for you in Excel, but I don’t use it so I wouldn’t know for sure.

Here’s an excerpt of recent runs from my Calc Running spreadsheet. We’re going to be looking at how to arrive at the column at the far right, Min/Mile. We’ll be using the bottom row as our discussion row.

To answer your question, the Dec column is the decimal result of dividing time by miles. In the bottom row that number is 9.73. I used that number as my Min/Mile for a while, but hey, we’re talking about running here! It’s got to be accurate in minutes and seconds!

Here’s the Calc formula for Decimal column. The row is [52]. We’re multiplying the time by 1440 and dividing that by the distance.

=(F52*1440)/C52

The Time column is a “Time” value, and 1440 is derived from 24 hours in a day, and 60 minutes per hour, thus 24*60=1440.

Here’s the format for our Time column in Calc.

Okay, back to our Calc spreadsheet and calculating minutes per mile. Here’s another look. We’re after the “9.44” in column [H52.]

Without further delay, here’s the formula for calculating Minutes-Per-Mile, given our distance, total time, and decimal time/miles column. Our Dec column is [G52.]

=ROUNDDOWN(G52)+ROUND((G52-TRUNC(G52))*60)/100

The result is the final–and accurate–Minutes-Per-Mile column, or 9.44.

Yeah, I know. Gotta get faster!