CrossFit Discussion Board  

Go Back   CrossFit Discussion Board > CrossFit Forum > Nutrition
CrossFit Home Forum Site Rules CrossFit FAQ Search Today's Posts Mark Forums Read

Nutrition Diet, supplements, weightloss, health & longevity

Reply
 
Thread Tools
Old 12-09-2008, 09:05 PM   #11
Frank Dennis
Member Frank Dennis is offline
 
Frank Dennis's Avatar
 
Profile:
Join Date: Dec 2008
Location: Indianapolis  IN
Posts: 1,162
Re: Refinement of a Nutritional Tool

Greetings!

The positive feedback on this has been great! I'm glad everyone who's gotten a copy seems to be finding it useful.

As posted above, I followed my trainer's advice, and the advice of some others from this thread I created: http://board.crossfit.com/showthread.php?t=40048 (wfs), and have begun using the measurements listed in the CFJ Meal Planner rather than those I calculated out to Zone "specifications." However, once I have these determined, they go into the spreadsheet as mass values, measured in grams, so you don't have to re-measure every time you prepare a meal. All you need is your scale.

Also, I found a site which seems suitable to host the sheet. The spreadsheet is located at http://board.crossfit.com/showthread.php?t=40048 (wfs)

Drop.io looks nice because it didn't require any personal info or paid subscription from me, but I can still password-protect the file and ensure that you all won't be getting horrible malware from a tainted version of my file. Follow that link and select the download option below the filename.

Once the portions are completely updated, I'll re-post the file. In the meantime, the spreadsheet posted is the original version. You'll probably want to re-measure, as I've done, but those quantities are actually more precisely on the Zone model of 4g protein / 9g carbs / 1.5g fat per block than the ones in the Meal Planner. However, everyone seems to be doing just fine on the Meal Planner measurements.

Also, here is a brief set of instructions and descriptions of the sheet. This went out in email form to all who've requested a copy (and if you're still waiting in the queue you'll get one, but moving forward please just grab the download link above).

Here we go, a page-by-page breakdown of the spreadsheet:

Nutritional Info -

This page, of course, is the list of food items. Everything on here is paleo-diet friendly, except for four things which are marked as such. I added those because, personally, I can't afford to go fully paleo at the moment.

Each itemís quantity, and corresponding nutritional information, is calibrated as 1 Zone Block of its particular category.

You'll notice that the column for potassium has a border running down the right side. This marks the last cell in the row which you should copy onto each day's menu page, starting with the left-most Item name column. When you look at the menu pages, you'll see that, immediately to the right of the "blocks" column, the next 13 columns match the first 13 on this page.

I used the method of taking each foodís Glycemic Index, dividing by 100, and multiplying by the number of grams of carbs to get the glycemic load values. Finding one complete resource for GI listings was impossible, so some of those might not be completely accurate, but I think they're all pretty close.

The last column before the notes column is calculated by me, at my local market. Obviously, you'll have to do this for yourself to get an accurate Shopping List. The column is called cost-per-gram, but those things, like eggs or milk which arenít measured in grams, are calculated appropriately here, currently. A lot of these items are new to this version of the spreadsheet, so cost values are missing, but I donít mind sending it out with this column blank Ďcause yíall are gonna have to go do the legwork on this yourselves, anyway. If you save supermarket receipts, lucky you.

Below row 76, you'll notice a dark border. This marks the last row on this list currently configured for the rest of the sheet to accept. What this means is, if you add to this list, all new items you add below row 76 won't show up in your shopping list, though you could put these added items onto the daily menu pages and the nutrient and block totals would add up just fine.

If you want to edit or change the items currently in the list, that's fine. I used www.calorieking.com (wfs) and www.nutritiondata.com (wfs) for the nutrient data, and Googled resources for the GI. If you want to increase beyond 73 the number of items which will populate your shopping list, this means adding columns to the matrix on the daily menu pages (we'll get to those in a moment), and if you're slick with Excel, or alternatively make a backup copy and experiment 'til you get it, this isn't that hard to do, just a bit tedious. Email me and I'll help you out, if you can't get it sorted yourself.

Sunday to Saturday Menu Pages -

These are broken down into four meals and one shake, mostly because I planned it for myself, and adding lines to the shake was something I didn't want to mess with. The shake on each day already incorporates 2 Blocks of protein powder, so you can add remaining protein, carbs, and fats on the three lines. You shouldn't have any trouble with the number of available lines on the rest of the meals unless you're eating really fancily (is that a word?).

The idea is, you copy and paste items into each row for each meal, and then, in the left-most column, enter the number of Blocks of that ingredient you're using. Note that the Blocks column will accept fractional inputs, so you can make a salad using a half-block of spinach, for example. The column doesn't distinguish between blocks of fat, protein, and carb, so a three block meal should total up as nine blocks.

At the bottom of each meal are its nutrient totals, and then at the bottom of the page are the daily totals. The quantity line of totals is pretty straightforward. The percentage-of-calories totals use the 4-calories-per-gram of protein and carbs, and 9-calories-per-gram of fat, convention to determine those values, so these are based upon actual nutrient content and not upon Zone blocks, which is why they don't work out perfectly. Also, the fiber content is removed in the equation, so you're only working on usable carbs. You'll notice that the Zone 40/30/30 isn't actually that ratio when you work this out, but who cares? Everyone's getting results with it, so I'm sticking with it.

Off to the right of the "Potassium" column are the hidden matrix calculation columns. You'll notice that the column letters jump from N to CJ. I keep these hidden to avoid accidental edits which would mess up the whole system, but all you have to do, if you want to see them, is select both columns N and CJ, then go to the "Format" menu, then "Columns", then "Show." If you're not confident in Excel, may I recommend that you save off a copy of the sheet before you mess with these.

What these do is test each row on the menu page for every ingredient on the Nutritional Info page (the only way to do it since you can only stack 7 IF/THEN statements in Excel). If they get a positive response, the quantities are totaled up and adding to the Shopping List.

Right now, there's room in the matrix for 73 ingredients, as mentioned above. The reason it's a bit of a chore to expand this matrix is, because it references some row and column locations which are static (the same for every referent cell), and some which progress (polling the "next-in-line" for a referent cell's row or column), it doesn't matter whether you turn the automatic formula adaptation on or off, the formulae won't work if you just copy the column next door and paste it over. You can do that, and it'll save time on doing it completely by hand, but then you'll have to go through and manually fix every formula in the column so that it works.

Shopping List -

This page is pretty straightforward, though it does have the most complicated (or at least most lengthy) formulae.

All the items, and their total quantities, from all 7 days will be totaled up here, and this page then takes the cost-per-unit from the Nutritional Info page and figures out your cost for groceries for the week.

Of course, this isn't entirely accurate in terms of the real world; for instance, you'll only be eating maybe 50 or 60 grams of almonds over the week (probably not even that much), so it's likely that you'll be buying several weeks worth of almonds at once. Same thing for stuff like olive oil, or protein powder. Still, I've found this tool to be EXTREMELY useful in budgeting my food costs.

Also, this page is meant to be filtered, so you don't print out a grocery list of empty lines. First, click the little grey square in the upper left corner of the sheet, above the row numbers and to the left of the column letters. This should select the entire spreadsheet. Next, go to the Data menu, and select Filter, then Standard Filter. The dialogue box which pops up is pretty straightforward, but Excel help should be able to walk you through it if you get stuck, or you can ask me for help.

Using the dropdown lists, create a filter which reads Column A <> to " ". That " ", should show up as an empty line in the third drop-down list. What this does is say, if in a given row column A is empty, don't show it. This will leave you with only your grocery list and the column headers to print.

That pretty much covers it, enjoy! If you have any ideas for improvements, or you feel like adding some rows to the shake meal, doing all the formulae, and sending it back to me, that'd be sweet

Cheers,
FD
  Reply With Quote
Old 12-10-2008, 06:34 AM   #12
Frank Dennis
Member Frank Dennis is offline
 
Frank Dennis's Avatar
 
Profile:
Join Date: Dec 2008
Location: Indianapolis  IN
Posts: 1,162
Re: Refinement of a Nutritional Tool

Oh, damn and blast...

As you'll no doubt have noticed, I missed the fact that I copied the wrong URL where I wrote "this is where the spreadsheet is located," and I was about to post the correct link here.

However, it no longer matters because, for some reason, the file is now completely inaccessible, even with the admin password I put on it (which it shouldn't be asking for, because I'm not trying to alter or replace the file, just access the download).

So much for drop.io.

Go ahead and keep PMing me for now, I'll email this to anyone who wants it. If anyone has another suggestion for a good free hosting site, let me know.

Cheers,
FD
  Reply With Quote
Old 01-03-2009, 02:00 PM   #13
Wil Rader
Member Wil Rader is offline
 
Profile:
Join Date: Dec 2006
Location: Las Vegas  NV
Posts: 26
Re: Refinement of a Nutritional Tool

I promised Frank that once I recieved a copy of this I would post it so he wouldn't have to continue e-mailing it to everyone, so here it is! It will remain here for as long as I'm alive to keep paying the bills and I'll update it if/when Frank sends a new copy. If anyone makes any updates, I will host that also, just PM me. It really is a great tool!

http://www.canbreakit.com/CFdiet_original.xls (w/f safe)

Wil
  Reply With Quote
Old 01-03-2009, 08:42 PM   #14
Frank Dennis
Member Frank Dennis is offline
 
Frank Dennis's Avatar
 
Profile:
Join Date: Dec 2008
Location: Indianapolis  IN
Posts: 1,162
Re: Refinement of a Nutritional Tool

Thanks Wil!

I am in the middle of updating the sheet; nothing major, but the conversion of volume-based (cups) quantities to mass-based (grams) quantities of the blocks listed in the CFJ Zone Meal Planner article is ongoing. When that's done, I'll send Wil a new one to post.

Also, a few items have been added to the list.

I sent everyone who asked for the sheet instructions on how it functions, I also posted them here. I've heard back from a few of you, but it would be great to get some feedback from more people about what would make the spreadsheet a more useful tool. Don't worry if you're not an Excel whiz; just let me know what feature you'd like, and I'll find a way to add it in, if it's possible.

Cheers!
FD
__________________
"It isn't safe out here. It is wondrous... with treasures to satiate desires both subtle and gross. But it is not for the timid!"
  Reply With Quote
Old 01-03-2009, 11:26 PM   #15
John S Park
Member John S Park is offline
 
Profile:
Join Date: Feb 2008
Location: Fullerton  CA
Posts: 278
Re: Refinement of a Nutritional Tool

Wow man.. this must have taken you a very long time!!

it is very detailed and nice. amazing work with that shopping list and weekly expenses
  Reply With Quote
Old 01-04-2009, 07:01 AM   #16
Frank Dennis
Member Frank Dennis is offline
 
Frank Dennis's Avatar
 
Profile:
Join Date: Dec 2008
Location: Indianapolis  IN
Posts: 1,162
Re: Refinement of a Nutritional Tool

Originally it took about six hours to put together; I didn't do it all at once, it was about six hours over the course of a few days. Originally, it wasn't set up for the Zone, though. Adding the Block multiplier and altering the formulae which total up for the shopping list took another two hours or so.
__________________
"It isn't safe out here. It is wondrous... with treasures to satiate desires both subtle and gross. But it is not for the timid!"
  Reply With Quote
Reply


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Online facility planning tool Paul Glasheen Running a CrossFit Facility 7 09-15-2008 05:27 AM
Furminator pet grooming tool Kelly Moore Stuff and Nonsense 2 06-03-2008 12:43 AM
Major Grip tool... Stephen Macioci Community 1 05-02-2008 08:06 AM
What's your favorite mental training tool/resource? Justin Gross Fitness 28 12-28-2007 06:06 PM
Cool Running tool John S. Powell Fitness 3 06-13-2005 08:25 AM


All times are GMT -7. The time now is 06:38 PM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
CrossFit is a registered trademark of CrossFit Inc.