VLM Training Week 8

VLM Training Week 8

So someone on Twitter this week pointed out that everyone who does triathlons bases all of their blogs on graphs, so something different this week. An actual real blog!

London huh?

When I was growing up my dad was into running. At the time I didn’t know anything about times or pbs, I just knew he ran quite a lot and at some point he ran the marathon in London. A year or two later he did it again. I remember looking at those medals in our cabinet and knowing they were something special. A marathon. No one else’s dads could run them but mine could. When he ran them we watched on the tv and looked out for him.  If you base your knowledge of marathons around sponsors these were the ADT years. The atmosphere, the start, the cobbles near the Tower of London. It was pretty compulsory viewing in our house and I loved it. I knew nothing about times obviously but I knew he was happy with them. Much later when I got into running I found out those times – 1st marathon 3:45, 2nd marathon 3:24. Not only did he run marathons he ran them bloody quickly it turns out.

Now, this is a problem for me. Why? Well whisper it quietly but I’m pretty competitive. Once you hear a family record time, it’s only right you want to beat it isn’t it?

Years later before I started running really, a couple of my colleagues decided to enter the London Marathon, which at the time I thought was madness. Neither of them were into running, neither had done it before. I followed them through their training and was impressed by the dedication and when they came back and told us their stories; the crowds, the experience, the pain and the joy of having that medal, well who wouldn’t want to do it? I entered the next day.

I didn’t get in.

Still inspired I entered the Paris marathon. I made up my training as I went along but as I signed up with a couple of colleagues I pushed myself quite hard. I really really really wanted a sub 4 marathon. Well, Paris was a wonderful, sort of a perfect day for running as I remember it. Everything went well, the weather was perfect the scenery great and the running good and fast. As it was my first I didn’t know it would hurt and I happily pushed myself. When it got bad I had wine at an aid station. Yeah, wine. I thought I’m 22 miles into a marathon, it hurts. Why the hell not. I walked for about 20m and it hurt so I went back to running which still hurt but at least it would be over quicker. And then as I turned the corner onto the home straight I knew I had done it, the clock was under 4 hours, in fact I finished in 3:57:16. I had put my mind to something and hit a goal on some made up training. Imagine what I could do if I really trained, my times will plummet surely? That’s what I thought anyway. The next year, I entered London.

I didn’t get in.

I signed up to Edinburgh and moved my training up a notch from Paris. Ran hard, ran more. Edinburgh is a flat course and I was going to go for it. Until a couple of days before the start I saw the forecast, and it was set to be a scorcher. 25C at the start line. We arrived the day before and it was boiling. Edinburgh in May, how can it be so freaking hot? It was a hard race, I went off fine, but at 20 miles it all got too much, I walked a lot and came in at 4:14. Bah. I entered London again for the next year.

I didn’t get in.

And then, in October 2011 I finally, FINALLY, I got my acceptance magazine. I’d be running in London in 2012, the Olympic year and it was going to be awesome. I’d let my running slip quite a lot by then so I resolved to run hard from October to April. And I did. Every weekend. I targeted a new Pb at half marathon distance in Jan and I got it. I got a tough training plan and I followed it. I ran lots of miles, I looked after myself. I did five 20+ mile runs and felt good in all of them. I ran way faster than I’ve ever run before. I’d trained hard, I was in shape. I was going to banish the demons of Edinburgh, I was going to get a good time. I trained for 3:40, ideally just a little under. I would take 2nd place on the Wilkinson marathon family tree. This thing was on!

And then… about 10 days out Pilla started coming down with a cold. It totally wasn’t her fault, her job exposes her to many more bugs and germs than most and this stuff happens. We fought it off for a bit but I figured I’d just get poorly and it would be done with at least I’d still have a few days to recover.

And then…. the weekend before I made an absolutely stupid decision. The kind of decision you hear other people doing and think ‘You idiot! Who does that?’. I decided to start replacing my kitchen.  It totally could have waited. This was a stupid decision for two reasons:

  1. it left me without a way to cook food the weekend before a marathon
  2. my dad helped me

Normally this would be great, but one this day he turned up with the start of a cold. After setting about destroying the kitchen, I was knackered and starting to feel very ropey. I got worse over the week until by the time Thursday rolled around I was knackered and out of breath walking to the tram stop (it’s a 10 minute walk). I got sent home from work. I might have cried a little bit.

By the day I was just about starting to feel better. I wasn’t feeling good but I decided to just go for it anyway. The day before I picked up my number at the expo and stuffed myself in the evening with the biggest pasta meal ever. I didn’t sleep much, way too excited. Before I knew it I was in the starting area desperately trying to soak up the atmosphere to remember it. I’d seen this start line on tv, I knew what it looked like and I loved it. We lined up in the pens, moved forward and booom, started. I was running ok, feeling ok and generally having a nice time. Too nice as it turns out.

Basically I went off way too fast. Absolute class A beginner mistake but I got carried away with the day and the race and feeling good. By the time I realised I was way up, we were getting to Greenwich at 6 miles, how could I slow down? The crowd was crazy, I was loving it and running fast. After about 9 miles I saw my support crew and was still smiling but it gradually got worse. I remember getting to Tower Bridge which should have been the absolute highlight, you should feel ecstatic going over that thing, it’s incredible, the people the noise, it’s something else. Instead I felt absolutely-f*cking-awful. I plodded on to about 14 miles, and had to stop and walk. What the hell? 12 miles out and I’m walking. Absolutely blown it. I tried running again and literally the moment I did I cramped up all up the back of both legs. I didn’t understand why this was happening. I was absolutely gutted.

That last 12 miles was the longest, hardest and worst running in any race I’ve ever done. If it wasn’t for the crowds I would have stopped. I almost did. A lot. In between all the dark thoughts ‘I just want this stupid thing over’ and ‘I’m never running again’ there were some absolutely stunning highlights. The bit through Canary Wharf where the whole MS Society cheering squad were screaming my name was incredible. The people who leaned over and patted me on the back and encouraged me. The crowd. The one lady who was shouting so hard for me I had to run and as I did I heard her go ‘YESSSSS’. It was incredible. It was awful. Simultaneously one of the best and worst days of my life. I saw my supporters at about 20 miles and gave them a big thumbs down sign. I still regret doing that because they were worried for the last hour or so.

I ran the last mile, well it was more of a hobble. Through the training I was imagining running through the line with a big smile, a goal achieved, a dream realised, but when I saw it I was just relieved it was over. I got my medal and barely looked at it. I still don’t. I’ve never worked so hard and been so disappointed in myself. This is not how marathons should end.

That was then and this is now. I’m going back, I’m training hard and I’m in a much better place than two years ago. It’s not going to happen again. Me and London have some serious kissing and making up to do. All the early starts, the long miles, the mountains of kit being washed, it’s going to be different this time. I know it.

Plus, my kitchen looks lovely.

VLM Training Week 7

VLM Training Week 7

Warning: there’s facts and figures a plenty in this weeks post….Been another fun week! Completed every planned session and even managed to add in a circuit one and a yoga visit. The excellent thing about this is it makes my pie chart look very colourful:

I was itching to run a bit faster this week so on one of the run commute sessions on Thursday I added in a 25 minute interval set in the middle of the run. I do really like the Chrissie Wellington ones, they force you to go quicker but they also make you think about cadence too (how quickly your feet touch the ground every minute). If you have quick legs you run faster for longer with less injury risk I think so it’s definitely worth doing. I definitely recommend the sessions. The run was pretty hard, the intervals go something like: 2 min, 2 min, 3 min 4 min, followed by rests and then 2 x 30 second sprints, with increasing cadence on each one. After the two minute ones I was only just hanging on, oddly enough the 4 minute one was the easiest of the lot. It’s a good intro into the faster work I have to do so I think I’ll keep it up on one session this week too.

Right then, speed. I want to do this marathon in around about 3:15. For me, that is fast! In fact it’s a bit freaking me out, but you need to have some stretching goals don’t you. So, now that’s decided, exactly how fast do I have to run? Well, it works out like this:

[table]Target Time,3:15:00

Pace,0:07:26
5km,0:23:07
10km,0:46:14
Half,1:37:30
Full,3:15:00[/table]

Yikes. Every mile in 7:26! Aargh! This is important for my training because I need to use it to practice running at that pace one session a week starting in two weeks time. According to the Don Fink book, I’ll be a good bet to hit my marathon time if I can complete those pacing sessions and if my long weekend runs are between 45 to 90 seconds slower than this. Well, now feels the right time to see a table of how I’m doing, doesn’t it?

[table]Week,SUM of Distance(miles),AVERAGE of Pace,SUM of Completed Time

1,4.25,0:09:16,0:39:21
2,6.97,0:09:15,1:05:16
3,13.82,0:08:42,2:00:28
4,19.29,0:10:18,3:35:23
5,23.33,0:08:29,3:16:13
6,29.48,0:08:39,4:12:36
7,35.32,0:08:13,4:51:26[/table]

Right now my entire weekly average for my runs is 8:13 min/miles. My Saturday run I averaged 8:23 but I did run the first three miles with Pilla a little slower than I probably would have run on my own. This is 60 seconds slower than my target time so I’m probably in pretty good shape.That also freaks me out because I might actually have to do this!

I’m training hard for this, I’ve never ever run so frequently and touch wood still feeling pretty good from it. I’m tired, yes, but I do feel in good shape. My running is getting up to where it was pre-ironman and I wasn’t sure if that would happen, and I certainly didn’t think it would happen this quickly. I’m probably risking over-training a little at the moment, normally Sundays would be a rest day but that is my swim class. If I was sensible I’d probably take Monday off (I know rest days are important, REALLY important) but this week I looked back at my training and realised I haven’t had a day off since Christmas Day. I quite like this, so I might just carry on!

Oh before I forget, I added this thing on the blog that let’s you sign up for email updates if I add a post. If you want to you can sign up below. Thanks for reading![subscribe2]

VLM Training Week 6

VLM Training Week 6

And that is the difficult festive season done! Definitely feel like I’m in the training groove right now. My weekly miles went up to nearly 30 which is the furthest in a very long time and I ran on 6 consecutive days which I don’t think I have ever done before. So far, still, touch wood and all that, it’s going pretty well.

I’m still in that base training phase, which is fine, I know it’s important to build your fitness steadily and get your body used to lots of running, long miles and the bigger effort ahead but I am totally itching to run faster again. I think I might *might* try a little this week to mix it up a little (cue a post next week moaning about being knackered and injured!)

I’m still thinking about what time to go for. In a couple of weeks I start building in some marathon pacing sessions mid-week which do exactly as they sound, you use them to practice your marathon pace. This is obviously important and something I’ve not focused on very much in the past. It’s also supposed to be pretty tough and I’m strangely REALLY looking forward to it (see the bit above about want to go fast!). My heart says go for 3:15, it’s the london marathon, I might never do it again and that would be an awesome time for me. My head says I’ve never been faster than 3:57 so go for sub 3:30 (still a good time, and my goal for the year). Ah bugger it, let’s just pick 3:15 and see what happens, hey.

[table]Week,Distance(miles),Time,Average Pace

1,4.25,0:39:21,0:09:16
2,6.97,1:05:16,0:09:16
3,13.82,2:00:29,0:08:42
4,19.29,3:35:24,0:10:19
5,23.33,3:16:14,0:08:29
6,29.48,4:12:39,0:08:39[/table]

The table above I’m currently slightly obsessed with. Ever since I linked the training spreadsheet to my garmin connect account I’ve gone a bit figure mad. My running should get fairly close to 5hrs this week and 34 miles or so I guess.

Pilla Training Plan

For all my love of a spreadsheet training plan, this week Pilla and I wrote up her plan on a napkin over a coffee and biscuits. She is doing the Silverstone half with me in 8 weeks time and is aiming for round about 2hrs. She’s had a bit of a foot niggle recently but that seems to have cleared up with lots of stretching and rolling (of both the tennis ball and foam varieties) and we managed to get out three times together this week, using two of my cycle/run sessions and we started out the long run together on Saturday. I think this was helpful to both of us, as I wasn’t really feeling it and it was her longest run for ages.

Pilla also made me go to swim class tonight.I was pretty done in from going to a wedding last night and having a couple of interupted sleeps (which really do have an effect on how much fun it is to go running in the morning, lots of sleep = good run) and wanted to give it a miss. It turned out to be pretty good mental toughness work because I wanted to give up pretty much the whole length of time I was there but I was too flipping stubborn to do that and by the time I got into the last 10 minutes I was on a pretty big high of beating the lazy Neil monster in my head.

The graph is really not very exciting at all other than to show the distances are gradually building and my average pace is falling a little. Good oh. Anyway I’m loving training at the moment. Bring. It. On!

Oh before I forget, I added this thing on the blog that let’s you sign up for email updates if I add a post. I honestly don’t know why anyone in their right mind would do this, but If you want to you can sign up here:

[subscribe2]

VLM Training Week 5

VLM Training Week 5

Training at this time of year is a bit weird. It plays on my mind on the run up to it, like it’s some sort of big hassle to get through what with all the eating, drinking and being merry. Once it comes around though it’s never really like that at least for me. Once you get rid of having to go to work for a few days the training becomes just part of the day – get up, get it done then come back and treat yourself to a nice breakfast. Obviously training at this time of year has another benefit – absolute guilt free eating. Another mince pie? Yeah, go on.

Week 5 is still another base level fitness week. I have about 10 of these before the speed stuff starts although I’m planning on building that in a little earlier as I have a pretty decent head start. I’ve moved up to running on 5 days a week and next week it’ll be 6. Half of those days my sessions will be half run, half cycle which appears to be working pretty well in keeping me uninjured (fingers crossed as I have a bit of an Achilles niggle but I’m hoping it’ll just disappear!).

[table]Date,Circuits,Cycling,Running,Yoga,Grand Total
12/23/2013,,0:23:00,0:22:00,1:30:00,2:15:00
12/24/2013,,,0:45:00,,0:45:00
12/26/2013,0:45:00,,0:45:00,,1:30:00
12/27/2013,,0:23:00,0:22:00,,0:45:00
12/28/2013,,,1:00:00,,1:00:00
12/29/2013,1:05:00,,,,1:05:00
Grand Total,1:50:00,0:46:00,3:14:00,1:30:00,7:20:00[/table]

So far, as expected really, I’m loving the Don Fink plan. I don’t know what it is but I just find his plans really easy to follow . I’ve struggled a lot following structured running plans in the past but the Fink ones just work for me. Added into the mix this week were a couple of circuit sessions with Pilla, these are usually ten different exercises we do for a minute each with a 15 second rest between them, which we repeat three times. This week we mixed it up a little by adding a bit of boxing in too. That really killed us, but in a good way!

So I don’t really have a lot to say this week other than I got it done, and I’m still really enjoying it. Who said this marathon training had to be hard?

VLM Training Week 4

VLM Training Week 4

I love having friends that do things that seriously impress and inspire you. I have some that build their own houses, some that volunteer their time to be a mentor to school children and some that just throw their arm in at anything and always try to be the best at what they do. Yesterday I was lucky enough to run with a friend doing an incredible thing, running all 47 miles of the Mary Townley loop through the hills just north of Manchester. Yeah, that’s 47 miles of hilly off road path through the hills of Lancashire on the shortest day of the year. This wasn’t some big event, or even anything organised, this was just someone wanting to raise some money by doing something huge on her own. It was a huge amount of fun to complete the last leg, running in darkness over the moors with some of good friends (one of them, Nic also brought some music – pootling down a bridleway in the dark, with the rain pouring down all of us singing Bob Marley was a pretty surreal and great moment!). Anyway, my rambling point is that I love getting fired up by the success of others, people who show you anything is possible is you just keep trying hard enough. You can read about Viv’s trip here and her sponsorship page is http://www.justgiving.com/getoutandgive-fft.

My training was pretty fun even without that trip. I’m four weeks in now and already feeling tons better about my running. One of the things I always forget is that is you consistently do anything you are going to get better, and running is definitely like that. In just four weeks I already feel pretty good. I did one of the Chrissie Wellington interval sessions on Thursday and although it was pretty hard I felt good and quick by the end of it. I also added in my first transition session since before the Ironman. I’m going to be doing three of these a week from now I think. If my body could cope with it these would just be low intensity runs, but I just think I’ll get injured if I do that, plus these keeps my bike fitness up a bit too.

My pace is slower this week just because of the hilly session yesterday and the transition session was not quick. I’m pretty happy with how I feel now the miles are getting into some decent territory.

My main issue this week is trying to figure out my target time and training paces. I always find it difficult that you are encouraged to pick these before your training starts because I find it really tough estimating what I can do. I want it to be hard enough to be a good challenge but achievable given my past horrible history with marathons. My half marathon best time is 1:36, but so this is roughly speaking about 3:20. If I set my sights on doing 7:30 minutes per mile in London that is a 3:17ish finish and is a nice round number to remember. If I pulled it off, 3:17 would be a PB by about 40 minutes. Eek.

[table]Date,Cycling,Running,Yoga,Grand Total
12/16/2013,0:45:00,,,0:45:00
12/17/2013,,0:45:00,,0:45:00
12/18/2013,,,1:30:00,1:30:00
12/19/2013,,0:30:00,,0:30:00
12/20/2013,0:22:30,0:22:30,1:30:00,2:15:00
12/21/2013,,1:00:00,,1:00:00
Grand Total,1:07:30,2:37:30,3:00:00,6:45:00[/table]

This week I also discovered the weirdness of Bikram yoga. We’ve been doing what I would call ‘regular’ yoga for quite a few months now and I find it both relaxing and challenging. I’ve not noticed a massive increase in flexibility or core strength but I definitely enjoy it and have been recommending it. Anyway, my regular teacher has finished for christmas already (seriously I am in the wrong business) so thought we’d try somewhere else. Bikram yoga is like regular yoga in almost no way. You go to an incredibly hot room full of carpets and mirrors, you get half naked and you spend 90 minutes with sweat absolutely pouring out of you while the teacher speaks at 100 miles an hour whilst not actually showing you any of the moves. It’s fun.

I actually did enjoy it, it’s a little more challenging than our regular place plus you definitely feel like you’ve had a workout after being drenched for 90 minutes, I’ll definitely be trying it a few more times.

So that’s my week. I’ve kind of fallen back in with training and am really enjoying it, there’s only 16 weeks left to London and I’m ready to hit this training. Oh, I also invented the above graph which shows how heart rate relates to pace over time. It’s rubbish now but I’m hoping eventually it’ll show I’m running fast for the same effort. Maybe.

The training spreadsheet, part 4: Linking Garmin Connect to Googledocs

The post you’ve all been waiting for! After all the fun of the previous three posts (one, two and three) you are ready to take your training spreadsheet to unknown levels of awesomeness. It’s time to link in your garmin connect account. Yeeaaaah, I said it. That is the dream I hear you all say. And it is, it really is.

Note: You might have to do quite a bit of messing around to make this work. I have found that it’s really helpful and quick now it’s working and it was definitely worthwhile, but you might well not. Just saying.

Before we go on any further, this is not for the faint hearted. If you try this yourself and get it to work I will here on in call you a spreadsheet ninja. I’m going to try and hold your hand all the way through, but things are definitely going to get a little scary for a while…

 

What we are going to do

We’ll take your googledocs spreadsheet (I’m not going to pretend I know how to do this in Excel) and:

  1. Configure your Garmin Connect account to make this work
  2. Add in a custom function to parse json files
  3. Add a sheet to store your garmin connect activities
  4. Create some vlookups to pull in the data into your training plan
  5. Create some formulas to mess around with that data

As always, I’m going to use this spreadsheet here:  http://goo.gl/Msd62O.

 

Configure your Garmin Connect

Garmin Connect has some API’s for pulling data from accounts. I got all of this information from this site: http://sergeykrasnov.ru/subsites/dev/garmin-connect-statisics/web_api.php. In order to make this work you need to have an open profile on Garmin Connect and all your activities need to be public. If you are uncomfortable with doing this then I’m afraid I can’t help you much. If you are happy with this:

  1. Go here
  2. Set both options for everyone to see

Right, let’s test if it works. Take this URL:

http://connect.garmin.com/proxy/activitylist-service/activities/username?start=1&limit=10

Change the username bit to your Garmin connect name and paste it into your favourite browser. What you should get back is a page that looks like this:

If you see any error messages it’s likely your privacy settings are wrong. If you see the above great! This is a JSON file showing your most recent activities. Cool, huh? No? Well it will be soon, just you wait.

 

Add in a custom function to parse json files

So now we have the data, we want to be able to load it into our spreadsheet. Googledocs have some flipping great import things (see here for a good overview if your interested) but sadly it doesn’t have JSON just yet. Thanks to some much brighter people than me, we can utilise a custom script to do this. This is much easier than it sounds. I got all of this from here: http://blog.fastfedora.com/projects/import-json, which is a brilliant overview of it. If you don’t want to read it, do this:

  1. Go here and copy all of the script you see
  2. In Googledocs go to ‘Tools’ then ‘Script Manager’
  3. Click ‘New’
  4. If you get a wizard menu, just click ok
  5. Delete any text in the window like (myFunction … )
  6. Paste that whole script in
  7. Go to ‘File’ then ‘Save’
  8. Call it ‘ImportJSON’

 

Add a sheet to store your garmin connect activities

Now add a new sheet in your spreadsheet and call it ‘Garmin’. Go to Cell B1 (it’s important not to use Column A, we’ll need it later) and paste the following in:

=importjson(“http://connect.garmin.com/proxy/activitylist-service/activities/username?start=1&limit=10″,””,””).

Change the username to yours again. You’ll get a ‘Thinking…’ message and then BOOOOOM! Your latest activities in the spreadsheet! Sweeeeeeeet!!!

You’ve got a lot of fields in there you don’t need and we only have the latest 10 activities. Let’s change that. Paste this in:

=importjson(“http://connect.garmin.com/proxy/activitylist-service/activities/username?start=1&limit=100″,”/activityList/startTimeGMT,/activityList/activityType/typeKey,/activityList/distance,/activityList/averageSpeed,/activityList/duration,/activityList/maxSpeed,/activityList/averageHR,/activityList/averageBikingCadenceInRevPerMinute,/activityList/calories”,””)

This is going to restrict the list to the fields I use for graphs and stuff, but mess around if you want more. You’ll need to figure out the structure of the JSON file but it’s pretty sensible. The ‘limit’ part is how many activities it brings back, now it’s 100. Bear in mind this is going to load every time we load the spreadsheet so it’s probably best to not make it too big, but you’re grown up, you decide (I’d really like to make it just pull in new activities, I should figure that out someday).

 

Create some vlookups to pull in the data into your training plan

Ok, we have the data now, but we want to add it into the training plan we created earlier right? We are going to use some vlookups to do that. Vlookups work by looking up a value in a range of cells and then returning a value from a column in that range. We could use date to do that, which would work if you only ever do one type of activity in a day, but this probably isn’t the case, especially if you have brick sessions from triathons. What we can do is use a combination of date and activity type (unless you do multiple activities of the same type in which case you’ll have to figure something else out) to do this.

In your garmin activities sheet, in cell A2, use the following formula:

=if(B2=””,””,CONCAT(DATEVALUE(B2),C2))

Copy it down to several hundred rows (at least as many activities you are planning on doing).

What this does is checks if there is a value in cell B2, if there isn’t it adds a text string with nothing in. If there is, it concatenates together the date value in B2 and the activity type in C2. I had some trouble with dates, so I had to convert the date into the DATEVALUE format too.

Ok, go to your training plan sheet now and after the complete column, add in these column names:

Distance(km), Distance(miles), Duration, Calories, Pace, Speed(mph), HR, Cadence

So here’s where the formula’s get a little silly, but let’s just make sure one works first. In your Distance(km) column, paste the following: =vlookup((DATEVALUE(A2),D2),Garmin!A:J,4,false).

This formula is looking up a value in your training plan based on combining the date and type, and looking for that value in the sheet called ‘Garmin’. If it finds it, it returns the value in the 4th column. Paste it down the rows. It should return some distance values if you have started your training. If it doesn’t it’s likely the date formats don’t match. Make your you change your training plan date formats to match the Garmin connect ones yyyy-mm-dd. If that doesn’t work, make sure your activity type names all match up (use the garmin name).

If you have got this far, bloody hell well done. Go make a brew you’ve done the hard part.

 

Create some formulas to mess around with that data

In case you didn’t spot it earlier let’s look at that garmin connect data. The duration figures are all seconds and the distance is all meters. You probably don’t measure your workouts in those so we’ll need to do something about it. Also, you probably have some activities you don’t use garmin connect for but still want to measure speed, distance and calories. Finally on that lookup we created there are ton’s of N/A values (when the vlookup can’t find a match it returns N/A. That makes our sheet pretty untidy doesn’t it?

To solve all of that we need some formulas. I can’t promise these are perfect, but they work for me.

[table]

Cell, Formula

Distance(km), “=(if(isna(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,4,false)),,round(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,4,false)/1000,2)))”

Distance(miles), “=if(K2=””,””,K2*0.621371192)”

Duration, “=(if(isna(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,4,false)),,vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,5,false)))”

Calories,”=if(isna(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,4,false)),,round(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,round(8,0),false),0))”

Pace,”=if(M2=””,””,((M2/L2))/86400)”

Speed(mph),”=if(M2=””,””,round(L2/(M2/60/60),2))”

HR, “=if(isna(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,4,false)),,round(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,9,false),2))”

Cadence, “=if(isna(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:J,4,false)),,vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:J,10,false))”

[/table]

 

It would take me ages to explain these but it’s a mixture of looking up values and doing some maths on those values. The isna test let’s me return an empty string (“”) if the vlookups fail to find anything. Copy them down the rest of your training plan. You’re done.

These work pretty well for me. You might need to mess around with field types for rounding and the pace one only works if you set the data type to ‘hours’. But this looks pretty cool:

I’d now suggest you go crazy with your pivot charts with your new data (average pace vs average HR anyone?). If you find this useful, interesting boring or whatever, let me know. Good luck with whatever you are training for 🙂

 

Recent Posts