Ok, so you’ve got to grips with setting up your chart in ‘The training spreadsheet, part 1‘ and you know how to set up your plan. You’ve understood the absolute greatness of pivot reports in ‘The training spreadsheet, part2‘ for selecting and filtering data and putting charts together  and you have fully acknowledged the greatness of Googledocs and/or spreadsheets in general. You probably think that the previous posts will one day be recreated in some Hunger Game style trilogy of films one day. I know I do. Good. Now it is time to start analysing the actual runs. From here on in, this will be called ‘The fun stuff’. Again, i’m using this spreadsheet here: http://goo.gl/Msd62O.

Don’t get me wrong, the previous set up will work just fine. For ages that is exactly how I had my Ironman training plan (did I tell you I once did an Ironman?) but one day you are going to look at it and think ‘I wish I could do more’. And you can.

What have I done?

What I really want to do now is track exactly what I’ve done with some reports. I might like a little table that totals up all the workout’s I’ve done. Well, because we have that ‘Complete’ field in the training plan we can.

1. Make a new pivot report
2. Add the field ‘Type’ to the Row
3. Add Complete to the columns
4. Add Minutes to ‘Values’
5. Add ‘Type’ to filter and just pick a couple. I went for cycling, swimming and running

My pivot table now looks like this:

Column B is showing the total minutes with no N or Y in it (so workout’s in the future), N are the ones I’ve missed (tut tut) and Y are the ones that are done. Notice how there are total columns for both rows and columns. Sometimes this is useful, sometimes it’s not, so handily enough you can turn them on and off in your Report Editor window by clicking ‘Show Totals’. Brill!

Important point. You can have multiple fields in EVERY area on that report editor. We *might* use this later on, but right now, just take a second to appreciate how cool that report is. It’s summing up the minutes and complete status of every type of exercise  in your plan. If you wanted to you could do a little sum to see how far through it you are. And the best bit is that is automatically updates as you add your Y’s. Aces!

So we can report on work out’s we’ve done. Now if only there was a way to track our performance too? Wait a second, there is!

Performance

Quite simply what I want to do with my running is see if I’m a) getting further each week and b) getting faster. So to track this we’re going to need to add a few columns to our training plan. I’m going to add:

• Completed minutes. I’ll type in exactly how many minutes I did. (I’m going to be smug here and say you shouldn’t need this column because you should just do every minute listed in the plan when you start each session. Slacker.)
• Distance
• Pace

Now for the moment you can type in all of these manually if you want. If you don’t like formula’s (Lozza) just do that. If you do like formula’s well then we can automatically work out pace can’t we because it’s just a sum of distance and time. The formula I have in K2 is:

=if(and(D2=”Running”,H2=”Y”),(I2/J2)/1440,””)

Now, I only want to work out a pace if the workout is flagged as complete and the workout is running (I’ve yet to work out a measure for speed of yoga. Damn you yoga) so I have an ‘if’ statement checking that. I then divide distance by time in minutes, and divide the whole sum by 1440 (the number of minutes in a day). I format the whole cell into the ‘hours’ format in Googledocs and it gives me my pace per mile. Trust me it works. I copy that down the whole spreadsheet.

So now I know how fast I’ve been on every run. I know what you’re thinking. You’re thinking, ‘why don’t we put that on a graph and cross reference that with how far we’ve run?!‘ and that’s why I like you. Well, that and you smell nice. Yes if we do that graph it might just tell us if we’re getting fitter and faster. Cool!

Right let’s get on it. Add another pivot report, select your data, go to ‘Data’ then ‘Pivot table report…’. Let’s think about what we want first though. We might want to break it down by weeks, and get the average pace for those weeks with the total distance, for only the running activities we have completed. So….

1. Add ‘Weeks’ to Rows and remove the total flag
2. Add both ‘Pace’ and ‘Distance’ into the Values section (order is import here)
3. Add both ‘Type’ and ‘Complete’ into the filter section and filter by ‘Running’ and ‘Y’

You’re done right? Actually no. The pivot chart defaults to summing all the values together. This is exactly what we want with distance, the grand total of all your hard earned miles over the week. But in pace, we really want an average. So go back to the values box for Pace and change the ‘Summarise by’ from ‘SUM’ to ‘AVERAGE’. Highlight the whole column and make sure the format is ‘Hours’ too (highlight the column, go to ‘format’, ‘number’ then ‘hours’. Taaa daaaaa:

(note, this isn’t quite perfect as not every run is equal, I could skew the figure by going and sprinting for 100m, but my training isn’t really like that, so it’s fine for me.)

So this is a nice table but a graph sure would be prettier. Select columns A through to C and go to ‘Insert’ then ‘Chart’. Do this:

1. Select ‘Use row 1 as headers’
2. Go to ‘Charts’ and select ‘Line’ then ‘Combo chart’
3. Go to ‘Customise’ and then scroll down to ‘Data series’
4. Select ‘Data series 1’ then set to ‘Right axis

What we want (who am I kidding, what I want) is a bar graph with the left axis the distance, and the right axis my pace. I want bars representing the total distance and I want a line representing a pace. As the weeks go up my distances should go up and my pace should come down.

Now I can go and do the same thing for cycling maybe and start building lots of other fun charts and tables. Go on, do it. You know you want to.

All this is pretty cool, but I’m lazy. What I really want is some way of automatically linking my Garmin Connect account to my plan because I don’t want to have to write anything in about my training. That is what computers are for… Well there is a way to do that too… The training spreadsheet, part 4

So you’ve read the primer post here ‘The training spreadsheet, part 1‘ and now your ready for some fun stuff, plugging real life data in and doing some fun charts and tables. Yeah fun with spreadsheets, that is what I’m talking about. Just as a re-cap, the spreadsheet I’m using is here: http://goo.gl/Msd62O

Let’s jump straight into a pivot chart. I’m going to explain this with googledocs but it’s very similar in excel too. Pivot tables and charts are a way of having your spreadsheet aggregate, sum or analyse your data. It’s a way of saying ‘find me all the data that looks like this, and do this with it’. So how does it work with that list we created?

Let’s say we want to create a graph totalling all the running we’ll be doing every week. We probably want a table that looks like this, to make a nice graph:

[table]

Week, Minutes

1,50
2,85
3,120
4,135
5,150
6,165
7,210
8,240
9,270
10,300

[/table]

This is a table that puts the week numbers in rows, filters just the running activities and then sums the minutes. Let’s go!

1. Select the data you want. You can either select or the columns, or in googledocs just click anywhere in your training plan.
2. Go to ‘Data’ then ‘Pivot chart report’

Done! Ok not quite, but that is the hard part. What you’ll have is a blank pivot table in a new chart plus a ‘Report Editor’ on the right. This report editor is what we’ll use next. If at any time you cant see the report editor, just click anywhere in the pivot table and it reappears.

We said initially we wanted the week number in rows. So go to ‘Rows’ in the report editor and click ‘Add field’. Select week. This will populate all the week numbers in your chart.

Next we said we wanted to filter it to just running. Because any good training plan has more than just running, right? So go down to ‘Filter’, click ‘Add field’ then select ‘Type’. Now on the filter box where it says ‘Show’ just select ‘Running’.

Last bit, we just need to populate some values. So go to the values option, add a field and select minutes. By default the pivot chart is going to now sum up all the minutes for running, and group it by week. You have your first pivot chart. Good, huh? You can thank me later, this will change your life. I think you might tell your grandchildren about this moment.

More complex stuff

So that was a very easy one, but you can see how easy it is to build up reports. Let’s quickly edit this one now, because we’re not just interested in running are we. No siree. We want to see every type of activity we’re doing over the weeks don’t we. In fact we probably want each activity in it’s own column, again with the weeks down the left. And obviously we want a chart too, don’t we?

Let’s add ‘Type’ into the columns part. Once you’ve done this go down to the filter and add ‘Cycling’ and ‘Swimming’ to your selected list. Boooom! you have now got a pivot table showing the activities you’ll be doing summed by type and grouped by week and with a total for each week.

Charts

To add a chart, highlight the first four columns, and go to ‘Insert’ then ‘Chart’. Pick the area one. Select ‘Use Row 1 as headers’ and ‘column a as labels’. Click insert. You are done. The graph looks like this (you should always set axis titles, unlike me here):

 

The best thing? If you change the values on your training plan, this graph will magically update automatically. Try it. So now we can do some charts, let’s look at how you might report on your progress next with ‘The training spreadsheet, part 3‘.

Training spreadsheet

This post is going to tell you all about setting up the ideal training spreadsheet and why you need one. Before I begin, let’s get a couple of things out of the way:

1. I like training
2. I like spreadsheets
3. I like graphs
4. I may well have shown more people my ironman training spreadsheet than my medal

If you don’t like any of these things, these posts are not for you! What I’m going to attempt to describe is setting up a marathon or other training plan on a spreadsheet, doing some reporting and best of all linking to your garmin connect account so you can really see how your training is getting along. This splits nicely into three posts, which are pretty much easy, medium and hard. Anyone will be able to do one and two, three is for your spreadsheet ninja’s. Have a go sure, but don’t come crying to me when all your cells have gone on the wonk.

Why do a training spreadsheet?

Well you’re going to be training for a marathon for 16 weeks, or an Ironman for 30+. You need to keep track of what you are doing, what you have to do and log where you have come from. Spreadsheets are easy to write, easy to update and can produce pretty graphs. I did all of my stuff using the googledocs spreadsheet. I would hugely recommend this because:

1. You can access and update from anywhere. You have no excuse to miss your training.
2. The graphs are like, really, REALLY pretty.
3. You can share your spreadsheet with people to show off how pretty your spreadsheet is.
4. You can publish the results direct to your blog.
5. It’s free.

See, isn’t it pretty? Also, aren’t I doing a nice mix of non-running stuff? Yeah, go me!

If you’ve not used it before, well we probably can’t be friends. Oh ok, maybe we can, but I’m not sending you a Christmas card. Googledocs is here: https://docs.google.com.

Before we go any further let’s talk about data.

Spreadsheets are super flexible and there are 100 ways to set things up. I’m not going to tell you my way is the best (you’ll just have to figure that out on your own), but what I will tell you is that a really common mistake is mixing up the data in your spreadsheet with presenting that data. We are not going to do that.

When setting up your beautiful new training plan, imagine the kinds of thing you might want to track. I would guess it’s going to be something like:

1. How many miles/minutes you have to do
2. How far you have done
3. How fast you are
4. When you have to do it

We really only want to list this stuff out once, but our reports might change based on how far through the plan you are or how boring you want to make your graphs (“Ohhh I wonder how far I’ve run on Mondays?“). There is one solution to this, a word that might make you run off screaming in fear….

Pivot charts.

There, I said it. I’ll talk about them in post two but let me be clear about this: If you are using spreadsheets and not using pivot charts, you are doing it wrong. When you do this once, you will laugh and look down on everyone that can’t do them. You’ll impress people at work, you might get a pay rise and you’ll almost certainly be more attractive. Well, to me anyway. The best thing? It’s really easy to do.

Still with me? Good.

So what does this magical spreadsheet look like:

Ok, it’s not that magical now, but it does have some key things:

1. All of the data is in one list, right down the page. There are a hundred or so rows.
2. Each bit of exercise is in it’s own row
3. Everything I might want to report on (weeks, types, time/distance) is in this list
4. The details column lists the type of run or workout
5. Half of the fields are calculated

What’s that, calculated? Yeah I obviously don’t want to write stuff out more than once if I don’t have to. I’m lazy (plus it’s very error prone). So Day is calculated off the date column, week number is too. The total number of minutes is also calculated from the time field. I should point out I do ALL my training on times not distances. It’s ace and you should try it. Plus it works great for figuring out if you are going faster (by using a graph or the old fashioned method of ‘I’m sure I only ran as far as that bus stop last week’).

After adding a date field in A1 (which you could automatically do, but for a couple of reasons it gets annoying so I’d advise against it). Again, the formulas are because I’m lazy and I mess around with dates and times quite a bit. You could easily just type it in and it would be effective too. The calculated fields are:

Week: =if(C3=”Monday”,B2+1,B2). Which is an if statement that says if C3 = Monday, up the week number in B2 by 1, if it doesn’t just use the value in B2. You have to manually start with 1 in Cell B2, and the formula starts in B3.

Day: =text(A3,”dddd”). This is looking at the date value and returning a text string for the day.

Minutes: =if(E3=””,””,(hour(E3)*60)+MINUTE(E3)). This is taking the time formatted value and converting it into minutes, if a time value exists. I do the if statement to make it neat if I need to add rows, or do activities with no time, like rest days. I’m adding a separate minute field just to ease some calculations later on. It’s easier to work with than a time formatted field in my opinion.

The spreadsheet sample is here: http://goo.gl/Msd62O

So now we have the basic format all ironed out, it’s onto the fun stuff….. reporting. Let’s move to ‘The training spreadsheet, part 2‘.