So what was I doing last night at 1 a.m. goofing around on Google Docs and Spreadsheets? And what does that have to do with this quasi-running blog, and why should you care? Last things first: you shouldn’t care, but I’m going to tell you.
See, I got it in my head that I’ve been getting slower the last few weeks. Not as slow as … well … you, but definitely slower. I know, I know, blasphemy you say. But it just really feels like I’m struggling every time out, and when I’m struggling there’s always one thing I do: stay up way too late goofing around on the computer. That in itself probably makes me even slower, and stupider, but it’s what I do. Anyway, I wanted to start a training log and plug in any hard numbers I could scrounge up from the last few months and see if there was a trend towards slowness. Maybe I’d even use this log going forward. Nothing like hard data to get one focused.
I know, there’s lots of training log websites and software out there that I could use and it probably would work fine. Though I’ve seen some of them and they’re not at all what I want or like. And I’m a website guy and I figured I could knock something up on my own in a night and it would do exactly what I wanted it to, with the exact simplicity and features I require. But that, you know, takes work, so the more I thought about it, the more I became disinclined to go down that road again. I build web applications day after day, and frankly, I’m sick of it.
So next I thought I would just use Excel to track my training numbers. But then there’s the whole online/offline bit and accessing it from anywhere and sharing it on my website and yada yada yada….. but I gave it a go:
As you can see, I like to enter my times like this: 6.24, 6.10, 6.14, 6.11, 5.59 … which just happens to be my splits for yesterday’s mile repeats, but which also, when averaged in a spreadsheet, are calculated as decimal numbers and not minute:second times. So the averages get off. Obviously there is no 5 minute and 82 second pace. I’m sure there is some formula or trick to convert, and I spent a while in trial and error, but remember, I’m getting stupider as I get slower and I couldn’t figure it out.
Anyway, this is a long-winded way to get to my original point. It struck me that I could use Google Spreadsheets, access it from anywhere, probably use their API to display some numbers and maybe build a graph on my blog like the one above. Ouila, problemo solved. Anyway, it didn’t quite work out. They don’t have an API for Spreadsheets. They have a RSS feed for their Documents portion, but nada for Spreadsheets. And only people with Google accounts can see the Spreadsheet (though anyone can see their Documents) so I can’t even put a link to it on this site. And I couldn’t figure out the averaging thing.
So my problem remains. Maybe I’ll just sign up for one of those training websites, though more than likely I’ll just live with getting slower, while you’ll have to live with me getting less un-stupid.
wow, did you really spell “voila” as “ouila”?
Oui je.
You probably know this but use the INT function. Entering your times like min.sec is useful because you can manipulate it easily. You can also change the character used to represent the decimal so if you want to go back to min:sec you could.
There’s probably a function somewhere that does this but I would convert min.sec to min.fractionalmin like so:
=INT(min.sec)+ (min.sec – INT(min.sec))/60
(however my EXCEL doesn’t do this right and messes up the type conversion from integer to floating point. NO surprise there. It is EXCEL. Better Idea – screw Excel and use a real tool. But if you have to use excel, if you break up the calculation and put the (min.sec – INT(min.sec)) into its own cell and then in a new cell divide that number by 60 it will work.
So that will get you into real decimal time so you can play with your “there must be something wrong with your watch” splits. Then to show your results in min.sec you just do more or less the same thing except multiply the decimal part by 60, divide by 100 (or simply multiply by .6), add it to the integer part and you have min.sec. Or you could concatenate the integer part and the seconds part with a period in the middle, but then you’d have text which you could then convert to a number.
Now if you’re really going to be of service to the community you make it handle hours.min.sec, but then I suggest you use PERL or Python or something.
Finally, to paraphrase one of the greatest books ever written, Once a Runner, by John Parker, “Your training is like climbing a ladder, except this ladder is really more like a roller coaster track — some of the rungs actually take you down but you’re building momentum to climb higher rungs” or something like that.
Thanks Noel. It’s not the math that is the problem. I’ve got that figured out. It was just translating it to Excel Formula-speak and getting Excel to cooperate that had me stumped.
PERL or Python huh? Those two languages are too far above this meager PHP guy. ;) Seriously though, I’ve already got something brewing in PHP, so we’ll see.
In excel I enter my times as hh:mm:ss.nn (I know .nn is going overboard) You can then enter normal formulas such as =time/milage or =average(t1:t9) =sum(splits)/miles.
Does that simplifying anything?
TA DA! PRESTO! BAM!