Monday, August 13, 2007

Dietary Nutrition Calculator

In the quest for good health, getting a nutritious diet is the best foundation. To that end, I have developed an Excel workbook that calculates the approximate amount of nutrients in the food that you choose to eat and compares it to estimated dietary intake references. The nutrient data is primarily from the USDA food nutrient data base, supplemented with some additional data from other sources that are noted. The food nutrient data are very approximate, so this only provides a rough estimate of what is in the food that you eat. Also, nutrient absorption and utilization by the body will vary, depending on the food, combination of foods eaten, and your digestive health status. So, even though the nutrients are in the food, that doesn't necessarily mean that your body will be able to fully utilize them. But at least you have given it the opportunity :)

The Excel workbook has nine worksheets. It opens with the Result sheet, showing the results for a sample day of food. The Detail sheet shows even more detail, including individual fatty acids and amino acids. The Input sheet allows you to input the amounts of food that you have eaten or plan to eat. Be sure to delete all the sample entries before adding your own entries. You can use formulas to prorate amounts over a longer time period to a daily basis for comparison to the dietary references. The Calc sheet performs the calculations using the input amounts and the nutrient data in the Data sheet. The Calc sheet can be reviewed to see which foods are contributing what portion of the nutrients in the foods you are eating. The Data sheet has nutrient data per 100 grams of each food. It can be used to compare the nutrient density on a per weight basis between different foods. The Data by Calories sheet has nutrient data per 100 calories of food and can be used to compare nutrients for different foods on a calorie basis. The Load sheet provides assistance for adding additional nutrient data obtained from the USDA food nutrient data base. Follow the instructions on the sheet to load new data. More data loading tips and information are in the Notes sheet. And finally, the BMR sheet will calculate the target number of calories for your ideal weight, based on your height, age, and activity levels. It uses the Harris Benedict Basic Metabolic Rate (BMR) formulas.

Download the Dietary Nutrition Calculator

Click on the link above to download the latest version of the Excel workbook. This link will take you to the MediaFire file hosting site where you can download the Excel file free. Wait for the "Click here to download.." box to appear and then click on that link to download the file to your computer. To return here, use the "back" button on your browser.

If you don't have Microsoft Excel, you can also open and use the Excel workbook with the free Open Office spreadsheet program that can be downloaded here:  Open Office

Good luck in your quest for health!

10 comments:

dave said...

fantastic spreadsheet. cheers mate!

Anonymous said...

Thanks for sharing this!

Anonymous said...

So helpful.. thanks for sharing this!

Robert Campbell said...

I like the calculator but I don't understand these instructions for adding more foods. I think it's ingenious that you used a @LookUP because the USDA does not keep exactly the same items. Can you explain further what you mean here?

"Then click on the 'Load' sheet to de-select the other sheets. Copy and paste data transposed from the Load sheet and enter the source, amount, quantity, weight, and food type at the end of each row."

I've been trying to cut and paste from the USDA website.

Bryan - oz4caster said...

Robert, thanks for trying out the spreadsheet. Unfortunately, the USDA changed the format of their food nutrient data displays, so these instructions for the old format no longer work. I have figured out a way to load data from the new format but have not gotten around to putting the new version of the spreadsheet with instructions out where it can be downloaded. I hope to do that sometime in the next few months so stay tuned.

Robert Campbell said...

I see what you mean. They added a hyperlink to their source. I'll play around with it. Thanks for sharing this. You did a great job.

Robert Campbell said...

I don't see any other way but to edit each name. I'm willing to do that, but now I'm confused about how to paste COLUMN C into the ROWS on the "Data" sheet. Is there a way to paste Columns to rows?

Robert Campbell said...

Maybe there was an easier way but I figured it out by saving the long form to a CSV. Copying that into LOAD then special pasting column C to a sheet I designed to transform columns to rows. From there I special pasted the rows into Data. There are still a couple of minor repairs to do after that like copy a few formulas in the Data sheet after column ED, but you did so much of the work it seems like a pretty small effort on my part.

Bryan - oz4caster said...

Robert, I uploaded my latest version of the Dietary Nutrition Calculator workbook to MediaFire and updated the download link to the new file in the blog post. I have not pulled data from USDA in over a year, so I hope they haven't changed again. Try the load option in the newer version and see if it works for you.

Robert Campbell said...

Thank you Brian. I did not expect you to do this for me so quickly, so I took your ideas and wrote my own calculator. I wanted the inputs to be based on raw food data and measured by grams since I mostly eat raw vegetables. I'm not finished but I will share my spreadsheet with you when it's complete. I stole a lot from your sheets so I will give you credit for the help.