Saturday, January 16, 2010

I used to be a geek

…and it still pops up in my world. Today’s example:

As is pretty typical, our shiny new treadmill lets you adjust your speed in tenths of a mile.


Here we are at my slowpoke pace of 6.1 mph. That’s right, I turned on the treadmill JUST to take this picture for you.

Now, I am foolishly training for a half marathon later this summer, and I’m putting together a training plan – kind of a mashup of Galloway and Higdon. In a later post, I’ll probably write more about this, but for now, the important thing to know is that I’m definitely into these little micro-walking breaks that Galloway recommends. (It helps prevent injury by letting your muscles recover quickly before they get all tuckered out.) Last year, when I trained for my first 10-K, I’d run for about 8 or 9 minutes, and then walk for about a minute. I say “about” because I don’t wear a watch when I run.

But Galloway actually recommends that at my current speed, my walking intervals come more frequently – every three minutes.

Here’s his table:

galloway table

As you can see, he’s using pace (minutes per mile), not speed (miles per hour). How do I know what my pace is, given that our treadmill just tells us our speed?

Yeah, I know what you’re thinking – I’m running 6 mph, and that’s a ten minute mile. But a girl can dream, can’t she?

The kids today would probably just look this up on the google, and find a chart that correlates the two. Hell, I probably even HAVE such a chart in my copy of Galloway’s “Half Marathon: You Can Do It”. Or maybe there’s a feature on the treadmill itself that will tell me my pace.


I tinkered around in Microsoft Excel, and discovered that their worksheet functions don’t do the job in one go. I wound up creating this little baby in only seven steps:


You’re just dying to know how I did this, right? Roll up your sleeves…I’m going into geek mode. (You’ll either kinda like this, or you’ll find it excruciatingly dull.)

Step 1. Do the Basic Math


So this first step is simple – I’m just taking 60 minutes per hour, and dividing that by – in this case – 8.5 miles per hour. And I’m rounding it to two decimal points, because if I didn’t, I’d get this garbage of things out to the zillionth decimal place. I now know that in the unlikely event that I have the treadmill set to 8.5 mph, I’m going 7.06 minutes per mile.

But remember that’s in decimals. I want to know how many minutes and SECONDS that is. Put on your scrubs, we’re going in.

Step 2. Convert numbers to text. Excel distinguishes between numbers – which you can do math with – and text. You can do sordid things with text that you can’t do with numbers, as you’ll see in a moment.


The result looks the same to the naked eye - “7.06”. But because we’re now dealing with text, we can perform surgery on it.

Step 3. Scalpel, please. Extract the “7” from “7.06”.


This is actually slightly tricky, because all we know is that the minute is whatever’s to the left of the decimal point. But the decimal point might be the second, or it might be the third, character in. This would not be a problem for certain people who know they’re always going to be under 10 minute miles. Alas, we’re dealing with me.

So we gotta find the decimal point, using the “SEARCH” function. SEARCH will tell us the position of the little bugger. Then we’ll take everything to the left of it using the “LEFT” function. We’ll end up with the minute. For now, we’ll just set that aside, and we’ll get back to it later in Step 7.

Step 4. Now extract the “.06” from “7.06”.


This is actually simpler, because remember how we rounded everything to two decimal places earlier? We know that if we take the right-most three characters, we’ll have the decimal point itself, and the two digits. So here we are with “.06”.

Step 5. Use math to convert decimals to seconds.


Interestingly, even though we converted stuff to text earlier, Excel will let us perform arithmetic calculations on this, so without further ado, we’ll divide the “.06” by 60 to get the actual seconds. We’ll also round it to a whole number while we’re here.

Step 6. Formatting our seconds data consistently

See how that four seconds reads as just “4”? Well, we want it to read as “04”. So what we’re doing here is padding it with two zeroes to the left, and then taking only the rightmost two characters.


So in this case, “4” will be converted to “004” and then stripped down to “04”. I could bore you (no! really?) with more details about how I had to tinker with this to get 0 seconds to show up properly as “00”, but I will refrain.

Step 7. Putting it all back together again.


So here we’re taking the “minute” from Step 3, and mushing it together with a colon and the properly-formatted seconds from Step 6. We have successfully converted 8.5 mph to 7.06 minutes per mile to 7 minutes 4 seconds.

Yes, this is the sort of thing I occasionally do FOR FUN.


Charlie helped me.


I will now reward those of you who stuck around til the end with a picture of how the stain was pulled right off the concrete by a mat we had down on the floor of the bump out. We let the contractors know, and they’re going to call the concrete guy to come deal with it.


Boo Hiss!

No comments:

Post a Comment