After recently finishing the push up challenge, I found that I liked having a goal as well as a way to track my progress towards the goal. When I used to do a lot of adventure racing, I always had a goal on the calendar, and I had teammates I was accountable to… But with the knee problems, and now slowly recovering but not racing much, and not on teams at all, I have found that I am more likely to miss workouts. With all that in mind, I thought I’d set up some goals and track them.
At 1st, I started to do a full year’s worth, but that was a bit intimidating so I moved to a quarter’s worth instead. But even then, I think that things are going to come up — family, business, injury, sickness, or just changing goals, so I made two goals — one fixed from the start, and one adjustable, in case anything comes up.
Here is what it looks like on google docs. Most columns and rows are self explanatory, but I will go through them below… First, let me talk about the tracking and the “on track” row. Here you can see I was not on track for running… All the other columns show a green YES, but that one shows a RED 21, meaning I was 21 minutes behind my target.
But then I ran 35 minutes and got back on goal:
So first on the columns:
- push ups — normal push ups, any hand position or variation
- pull-ups — both hands forward, both hand back (really a chin up) or alternate hands (supposedly works on obliques)
- dips –obvious
- running — obvious
- biking — obvious
- other aerobic — stairs, elliptical, hiking, walking, paddling, etc
- yoga — preferably video or class though I have my own practice too, that will go here
- bodywork/strength — time spent on pull ups, push ups, and dips goes here too, but this also includes physical therapy exercises (stretch, strength, core) and other such stuff
For the rows:
- goals (fixed) — the goal I set at the start of the quarter — this one I will not change
- percent done — total to date divided by the goal (fixed)
- on track (fixed) — whether or not I am on track to meet the goal — I’ll show some formulas below
- the next three for “adj” are the same as the prior three, just for the adjusted goal, if I make changes due to unforeseen circumstances… So far this Q I have not. 🙂
- days left — number of days left in the quarter
- total days in period — number of days in the quarter — I started 2 days early this time, thus the 93
WARNING: Stop reading here if you don’t care for math and spreadsheets!
Now, there are some pretty interesting formulas here, so let me walk you through them.
percent done — this is just the “total done to date” divided by the “goal” displayed as a percentage:
=B8/B2
total days in period — using dates in formulas like this was new to me, but pretty nifty.. Basically “total days” = “end date” – ” start date”
=DATE(YEAR(TODAY()),12,31)-DATE(YEAR(TODAY()),9,29)
days left — here it is just “end date” — “today”
=DATE(YEAR(TODAY()),12,31)-TODAY()
Second warning — it gets ugly here!
on track — here is where it gets interesting… let me start with the full formula then break it down
=IF ((B2/$B$25)-(((B2-B8))/$B$24)>0,”YES”,(B2/$B$25)*($B$25-$B$24)-(B8/($B$25-$B$24))*($B$25-$B$24))
The “IF” is just:
IF (test, then value, otherwise value)
So in other words, do some kind of evaluation, and if it is true, use the “then” value, and if it is false use the “otherwise value.” My test is (B2/$B$25)-(((B2-B8))/$B$24)>0) which basically breaks down to if the “goal divided by the number of days in the period” (which gives you a target count per day), minus the “total to date divided by the number of days left” (which gives you a required count per day for the remaining days), is greater than 0, then I am on track… So, if “target per day” – “actual per day” is positive, things are good and I put a “YES” in the value. Then I use google spreadsheets coloring to change the cell to green when ever it sees YES.
It may be best to work through an example… Lets use running, so for B change that to E. (For $B leave that as B!) I have 500 minutes as my goal for running, and the total days in the period is 93. So 500/93 = 5.37. That is my “target count per day,” in this case 5.37 minutes. If I ran 5.37 minutes each of the 93 days, I would reach 500. (Of course, I normally only run 1 or 2 times a week, so it is more like 36 minutes a week is what I need…)
Next we look at “total to date divided by the number of days left” to get a required count per day for the remaining days… In the 1st screen shot above, I had run 22 minutes, so I had 478 minutes left to do in the remaining days. 478/85 = 5.6.
Now, 5.3 – 5.6 is negative, thus I was not on track. Anything 0 or above is on track, and gets the big green YES.
Now rather than just put NO if I am not in track, I want to see how far off I am — or what my deficit was. So I have:
(B2/$B$25)*($B$25-$B$24)-B8)
Let me break that down down too…
That is the (goal divided by total number of days) multiplied by (the number of days remaining) minus the total to date. (The parenthesis are important!) Again an example would be useful, so going back to the screen shot where I had a goal of 500 minutes but had run only 22 in the 1st 8 days, and was therefore behind schedule…
So (goal/number of days) = 500/93 = 5.37. Multiply that by the remaining days, which in this case was (93-85) = 8, so 5.37 * 8 = 43… That is how much I would need to run per day to reach the original goal. But I have already run 22, so 42-22 = 21… (I am rounding so that shows up as 22 in the example above.)
Got it? 🙂 I admit it took some experimenting and trial and error to get these formulas right!
Now there is more to this — there are pros and cons to tracking this way, but I will save that for another post.
2sparrows 