Google SpreadSheet 61 day Tracker Instructions

[Ogedei]

61 Day Challenge Tracker.

Step 0: Green is good, white is bad.

In the tracker there are cells that are colored green. These are to be used to enter data. The white cells are either not used or contain formulas. If they contain formulas and you change them, the sheet will not work properly and will be a useless tool for you.

Step 1: Setting up the sheet for your use.

The first thing you will need to do is open the spreadsheet at the following location.
http://spreadsheets.google.com/ ccc?key=pcYKF234Hk5Ugwwq35c-IFw
This will open the master copy.
Click on "File, copy spreadsheet". This will make a personal copy in your google documents.


Step 2: Setting your goals.

a) Nutrition: We have 61 days to account for, each day has two segments. So we have 122 chances to eat well on this challenge. How many times will you eat well? My personal goal is 110. I enter that into the green target box for nutrition in the goal section.
b) Resistance Training/interval/cardio: How many times in this 61 day period will you train? 2x a week? 3x a week. I am aiming for twice a week so I enter 16 sessions in the Green target areas for the various areas.


Step 3: Tracking our progress

a) Personal goal: Very up in the air, track it however you want. I put a place for a basic description and some notes. if you type "yes" into the completion box it will add the points on to the total for you.
b) Competitive goal: Enter the name of the event, when it is, and if you have registered. Again type yes in the completion box when you are finished. An example is provided in the purple circles below
c) Nutrtion: Each day has a single spot. Enter 2 if you ate according to your plan the whole day or 1 if you screwed up a little and only managed to eat clean for part of the day. If you really screwed the pooch, enter 0. Nutrition is illustrated in the red circle below.
d) Fitness: on the seperate page each of the days has 3 categories, RT(Resistance), Int (Intervals), SS(Steady state). Enter the number of sessions you completed on the given day.

As the challenge progresses the tracker will tally up your sessions and compared them to the goals you have set and express them as a percentage. It will also caluclate the total points you have attained based on the multipliers discussed in the official rules.

If you choose to publish your tracker sheet you can make it viewable to everyone so we can view your progress on the fly. As you will find in my signature.

I hope this helps you use the google spreadsheet a little better.

Pros and Cons: The excel spreadsheet is prettier. The google sheet can be accessed anywhere.

Keith

[Fit4Him]

Thank you so much for that...Got mine!!

[James Newman]

Thank you, Keith. I agree the Excel is prettier but google docs will be more functional and in the end help save me some time in tabulation....

Newman

[Sign]

When I change the nutrition goal in E4 it is not updated in cell D22, it still says 110.

[Ogedei]

Damn.

All you need to do is open the spreadsheet for editing. Click on D22, type = and then click on E5, then hit enter.

If anyone has problems, feel free to add me as a collaborater (ogedei@gmail.com) and send me the link in a private message.

[ohammersmith]

Og, you're my hero. I was going to do this, but you beat me to it.

This opens up a lot of magic that can be done with the gdata spreadsheets API. I might be hacking on this this weekend.

[jj]

Nice job Ogedei, this is going to make things a lot simpler to track!

[Ogedei]

Thanks for the kind words everyone. I like spreadsheets, i am a nerd that way.

Although I noted yesterday that the tracker had already counted down to 61 days so I added a fix so it would work. Today I note that it should DEF read 61 days and it reads 62.

To fix the problem on your sheet do the following.

open the spreadsheet.
double click on B6
delete the +1
hit enter

All is fixed! I made the changes to the master copy already.
Worst case your sheet is off by a day on the days remaining. Not the end of the world.

[mom2w]

Thank you so much for this! I have MS Works so couldn't use the excel (but it was pretty to 'read-only'). I'm all set and ready to go now.

[vanessa40]

You are a lifesaver..thank you..

[UConnJulie]

Thanks Og!! I was completely clueless before, but this makes perfect sense now! Awesome! Since I will be offline for a good part of the challenge, I have saved it to my 'puter and will upload to Google docs when online ...
You da man!!

[SpacecityPaula]

Ogedei:
Does the fitness tracker sheet tie into anything. I want to add an additional sheet so I can track my number of meals carried per day and since the main area of fitness tracker sheet (where you set it up for resistance, Interval and SS) could be easily adjusted to that, I want to make sure that it doesn't tie into anything before I start entering stuff into it.

Thanks.

[BamaDave]

Thanks for setting this up, Og! Very helpful.

[Ogedei]

Originally Posted by SpacecityPaula

Ogedei:
Does the fitness tracker sheet tie into anything. I want to add an additional sheet so I can track my number of meals carried per day and since the main area of fitness tracker sheet (where you set it up for resistance, Interval and SS) could be easily adjusted to that, I want to make sure that it doesn't tie into anything before I start entering stuff into it.

Thanks.

It adds the value up on that sheet and transfers them to the summary section on the main page where it figures out your points.

At the bottom you can add new sheets and customize it a little. I have a couple of extra sheets added onto mine.

Hope this helps,

Og.

[SpacecityPaula]

Originally Posted by Ogedei

It adds the value up on that sheet and transfers them to the summary section on the main page where it figures out your points.

At the bottom you can add new sheets and customize it a little. I have a couple of extra sheets added onto mine.

Hope this helps,

Og.

I think I got it figured out so that it doesn't transfer stuff to the front page (at least it looks like it anyway )

[Victoria]

My tracker page is all set. Thanks so much for putting that together, Og. That's great!

[Ogedei]

Days Remaining Bug

PROBLEM: Days remaining are not calculating correctly

REASON: the function =now() returns a time with the date. Once we are past noon, the difference between the start date and the current date is rounded up, causing the days remaining to be off. ie our start date is June 1st. Currently it is June 2nd. June 2nd minus June 1st = 1. With the time involved once we are past noon June 2nd minus June 1st will = 1.5..... This will then be rounded to 2, and even though there is still 60 days remaining it will show 59.

FIX: Open Sheet for editing, click on cell B5 and type =today() and hit enter. This function just returns the date and should correct this minor issue. As always the master sheet has been updated as well as any sheets I have access to.

[LisaS]

thanks!

[Ogedei]

I just completed a new feature for the next challenge. I don't have it up yet cause I built it on my sheet.

The concept is to allow you to take the week, plan when and how many of each type of workout you want. Say put a 1 on Mon, Wed, Fri for resistance, and then it will calculate how many sessions that will be for the challenge.

Cool, non?

[Lost Dog]

Originally Posted by Ogedei

I just completed a new feature for the next challenge. I don't have it up yet cause I built it on my sheet.

The concept is to allow you to take the week, plan when and how many of each type of workout you want. Say put a 1 on Mon, Wed, Fri for resistance, and then it will calculate how many sessions that will be for the challenge.

Cool, non?

Oui. Especially since we started on a Friday and have 8.7 weeks in the Challenge. Three workouts a week. How many workouts is that?

That was worse than blocks...

[Ogedei]

Don't worry the calculator takes that into account. Since you actually place the workouts on the days it is smart enough to check and see if the first and last weeks are partials and if they are figure out how many workouts you are supposed to get based on the day.

Although I don't have it anywhere you can play, you can see the format I am working on in my challenge tracker spreadsheet.

[Ogedei]

Also....

If you are publishing your sheet, when you click on the publish tab, it defaults to publishing manually. So when you change something you have to republish. To fix that there is a second radio button below publish manually which is publish automatically. Click this and forget about it.

[ohammersmith]

Originally Posted by Ogedei

I just completed a new feature for the next challenge. I don't have it up yet cause I built it on my sheet.

The concept is to allow you to take the week, plan when and how many of each type of workout you want. Say put a 1 on Mon, Wed, Fri for resistance, and then it will calculate how many sessions that will be for the challenge.

Cool, non?

Slick. Do you have it as a single sample week, every week, or the "current" week?

[ohammersmith]

What's the simplest way to make the points total an integer?

I ask because I was going to look at fixing the problem with the days remaining... but my solution was way more complicated than yours. Og is the spreadsheet master.

[Ogedei]

For the points I just went in and reformatted them as an integer with no decimals.

Here is the newest version...

http://spreadsheets.google.com/ ccc?key=pcYKF234Hk5VfK_4kb1w6DQ

[ohammersmith]

Did you do anything with the remaining meal points to make it account for today's entry? For example, right now mine says 120 points left, but if I put in today's points, it stays 120. It probably should be 118, if I put in 2, 119 if I put in 1.

I'll see if I can't come up with a simple way to make that work.

[Ogedei]

I suppose you could do that, but really that field is just to show how many 1/2 blocks are left. A time field somewhere could be used to time those 1/2 blocks to change at noon. so at midnight you have 120 days left, at noon it would switch to 119. Since the days remaining should be based on the day ending, not really on what you enter, since you can enter 2 right now for today, but the day is not yet over.

Personally for the amount of functionality vs the amount of work, probably not something that will occur from my end anytime soon, well the time way would be pretty easy I guess.

[ljk]

I'l just live with the incorrect days tracked - does it really matter? I know hte date anyway.

[Ogedei]

Originally Posted by ljk

I'l just live with the incorrect days tracked - does it really matter? I know hte date anyway.

Nope, not really. And it will still be correct for half the day. Just for people who are interested.

Og.

[ljk]

How do you get the front page to get your fitness totals in? It doesn't seem to pull the data from anywhere? I'm confused.