TOPIC: Spreadsheet - BMR/TDEE Deficit, Macro Calcs, HRM zones
Joined Nov 2011
December 5, 2012 3:02 pm
Of course this is long, could I do anything but verbose?
"Why a spreadsheet? I already have several sites I go to to get this info and compare the differences and avg them out."
"I already use a BodyMediaFit or BodyBugg or FitBit or other and am given daily TDEE." (but is it based on most accurate foundation?)
Want to save your stats when changes occur and stop entering them and writing the results down each time?
Want a deficit goal that is not a straight block of calories or single % Deficit, but based on your workout type and time?
Want to see why there may be differences to the resulting figures between sites?
Want to be able to nail your level of activity better so you really have the best starting point to take a deficit from?
Want to be able to tweak MFP slightly but still eat back exercise calories?
Want to be able to convert macro-nutrient recommendations to MFP's % method?
Want to confirm or get your HRM better setup to reflect best estimate calories burned?
Want to use a Polar formula with best stats for calories burned estimate but with your HRM?
Want to train for endurance events, or have your workouts truly benefit from your hard work?
Want to figure out what you would likely eat at goal weight with current activity level, and just eat that now?
The spreadsheet will do all that. Built with the observation of many forum posts and seeing common problems and desires, and helping a lot of people. Mostly plateaus because of under-eating for the level of activity that is being done, causing the body undue stress and fighting weight and most importantly fat loss.
There is confusion out there getting some simple figures that really impact ability to make weight loss not as hard as some make it. I wanted the figures all in one place to see and compare, and save the stats compared to a website.
BMR (Basal Metabolic Rate) - what your body would like to burn if you slept deep all day and ate enough to not suppress it -
Harris formula based on study of healthy weight participants with avg fat mass, from 1919, and considered least accurate. Inflates the BMR estimate as fat% goes up. Uses age, weight, height.
Mifflin formula again based on healthy weight folks with avg fat mass, what MFP uses, considered about 5% more accurate than Harris. Doesn't inflate as badly as Harris as weight goes up. Uses age, weight, height.
Katch formula again based on healthy folks with avg fat mass, but uses the Lean Body Mass (LBM) only in calculations. Considered most accurate at all levels, though if you carry more fat than avg, it is slightly underestimated as fat is metabolically active.
TDEE - Activity levels to select Total Daily Energy Expenditure (TDEE) based on underlying BMR, which if suppressed so is this -
Harris table and multipliers is from same 1919 study. Confusing. Is 3 days walking 30 min the same level as 5 days running hard 60 mins, no. Is weekly running 4 hrs the same level as lifting heavy 4 hrs, no. What if work is active already as teacher, nurse, ect, then what?
World Health Organization (WHO) levels from 1985 study. Very detailed, takes a lot of thought, and all too easy for big changes in routine to change figures, making possible frequent updates.
Several studies that show how accurate walking burns calories, to compare to other activities that are about the same intensity.
Deficit - from TDEE - to lose weight, must burn daily more than you eat daily -
Straight block of calories no matter how much to lose. Is 1000 or 500 deficit daily appropriate for someone with 100 lbs compared to someone with 10 lbs to lose? No, too much deficit is counter-productive for fat loss, and maybe causes muscle loss or no loss.
% deficit method, 10, 15, or 20% options. 20% with little to lose means taking too big a deficit, at what point do you change so body isn't stressed?
No exercise should have least deficit to prevent muscle loss. Heavy cardio has same potential problem. But lifting heavy to maintain muscle allows the biggest deficit, besides using the least energy during the workout.
Bodyfat% - opposite of Lean Body Mass (LBM) which is everything but fat, including water, organs, bones, muscle, ect -
Body measurements the best not because of accuracy, though that can be within 5% if done correctly and body type measures well, but because with fat loss encouraged, inches lost may be more than lbs lost, if you are exercising and expecting improvements in the body.
Bodpod or hydrostatic weighing can be within 3% if done correctly, but expense and infrequent to see results, though great to start with.
Bioelectrical Impedance Analysis (BIA) can be within 5% depending on user following best practices outlined in manual, and can be very consistent showing direction even if not as accurate.
Calipers can be with 5% depending on user following best practices and in the hands of experienced person doing 7-site method. Great for showing progress too even if not totally accurate.
Dual-Energy X-ray Absorptiometry scan (DEXA or DXA) can be within 3% and while expensive can give extra results as to what is the LBM composed of, which everything but fat still leaves some variation in bone, water, muscle, or other.
Must copy the spreadsheet to change values. If everyone accessed the same shared spreadsheet, one set of values would be changed by someone else while you were using it, right? If you have Gmail account, you can Make a Copy. If not or you want Excel, Download as Excel. Link there to convert to older versions if needed. Designed for Google spreadsheet, some visual formatting is messed up in Excel and may have to adjust column widths.
Simple Setup tab.
Get and enter your stats, just read the instructions, when finished, don't forget to go to the Progress tab to log the stats with today's date.
You'll be given the best BMR based on if you did the BF% or tested RMR value, TDEE based on Activity Calculator, Total Daily Eating Goal (TDEG) based on amount to lose and ratio of heavy lifting to maintain muscle to heavy cardio that can burn it. And instructions on how to change and use MFP now, along with macro % figures to use based on your LBM or goal weight.
Instructions near bottom for alternate method of Eating for Future You, what would be your daily goal at goal weight with current activity. If you keep this level of activity, you literally could set that goal and forget it. Deficit will automatically become less and less as you approach goal weight as it should. And you get good at eating what you will in the future.
This TDEG is based on what ratio of heavy cardio to weight lifting you are doing. More cardio, higher TDEE and less deficit. More lifting is lower TDEE and more deficit. How? Lifting uses less energy during the workout, recovery uses more fat later. No need to replenish fat stores. A sliding scale based on %.
Additionally, the % Deficit method of doing total cardio or no exercise, is a sliding scale between 5 to 40 lbs to lose, from 10 to 20% deficit. More than 40 lbs is still 20%, less than 5 lbs is still 10%.
So you can see what tweaking your workouts to more lifting and less cardio could do for you, or walking more often, ect, and see the updates.
Simple way to show what your current stats are, you enter the important ones below before you update the stats and lose past ones. Enter the TDEG you really used, and get the stats from MFP for the time period before you update them as to what you really ate. Couple extra measurement fields you can name. Very useful to see which BF% calc is changing the most, the other is most likely more accurate. See inches lost. See if you met your TDEG decently by using table on far right to enter in up to a month's worth of actual eating level, to compare to TDEG. Can also do 4 weeks worth of eating, enter that and amount lost in that time, and you'll be shown what better TDEE estimate must have been, along with what to change in Activity Calc to make it happen.
MFP Tweak tab.
In case you want to start with more accurate BMR then MFP uses (Mifflin) and follow principles of not creating a deficit too large, but your workouts are variable for whatever reason, you'd rather just eat back exercise calories, but keep a safe calorie goal.
Stats from Simple Setup tab shown again, along with BMR's, and the Activity Calculator multiplier from what you did enter, in case there are some aspects of work or exercise that are consistent.
Enter the BMR to start with, and the multiplier to use. Appropriate deficit is taken, and MFP settings shown.
Additionally, should enter your workout calories from whereever you get them, and time, and those will also be reduced correctly so they have deficit too. If you fill in the HRM tab, you can also simplify and enter in time and avgHR for the workout and receive your most accurate calories burned to use.
Common recommendations for appropriate amount of protein to eat to help maintain LBM, and fat and carbs.
Enter in your goal, see the results, tweak as desired.
Figures will be given for converting them to the % method that MFP uses.
TDEE Deficit tab.
This shows your stats and the same BMR calcs you've seen everywhere, most accurate Katch-McArdle using weight and bodyfat%, to Mifflin and Harris using age, weight, height.
Included is the Cunningham RMR calc based on weight/BF%, and if you have gotten a measured RMR in testing, a place to estimate what the BF% would be from that, compared to measured BF%, tells you if your RMR is likely suppressed or not. If RMR is lower than estimated, don't recommend keeping it slower by eating too little. And if higher, no need to lower it by eating too little, in which case change the BF% in Simple Setup tab to amount shown, so higher BMR results!
Next is the TDEE calcs using several different methods. Pick one or compare.
Calc 1A is results from Simple Setup tab, with multiplier shown for comparison.
Calc 3B is most accurate if you have those devices and a HRM correctly setup for best HRM burns. Don't use HRM for lifting calorie burns, totally inflated.
Calc 2 useful if your HRM has been setup correctly.
Calc 3A if device is all you have, though they underestimate anything beyond walking/jogging/stairs type exercise, but if that's what you do, use it.
Calc 1B is the Harris TDEE calc most sites will use that you've seen, along with the multiplier.
You can fill in data for several of them to compare, keeping in mind what might be most accurate, and comparing the BMR multiplier shown. The lowest is not the most accurate, be honest.
Then are the deficit methods, the common 10-20% depending on amount left to lose, 2% of weight to be lost for truly obese, and 0.7% bodyweight based on study where people still gained LBM while lifting.
The Simple Setup tab TDEG combines the top 3 methods, if obese then 2% of weight to be lost, otherwise the sliding scale between 10-20% Deficit and 0.7% of current weight method, depending on amount of heavy lifting to cardio being done.
Included is a Calorie Cycling method based on lifting heavy with little to no cardio, but truly rest days. Best bet for true body re-composition. Lose fat on rest days, gain LBM and hopefully muscle from lifting days. Tad slower weight loss going for 2 goals, but great fat loss.
Also included is the Weekly Intermittent Fasting 5/2 method, eat at avg TDEE 5 days a week, and on 2 non-consecutive rest days eat at 25% of TDEE. Great if your metabolism has been messed up for along time as this gives so many days of being normal, but the avg weekly deficit is still 21%. Really works best if the 2 rest days don't follow a lifting day, where you would probably be extra hungry from that anyway.
Finally instructions on what to change where in MFP.
Allows using those devices with spreadsheet for these improvements.
You want the 15-20% deficit method used with TDEE Deficit, not the block of calories MFP uses (250, 500, ect).
You have many workouts that those devices are not good at estimating calorie burn, lifting, spin, rowing, ect, so you do want some syncing, but some manual logging, and want the same % Deficit method.
You want a totally static number to eat everyday, and do normal exercise included TDEE Deficit method avg out daily.
You want to use the Simple Setup tab recommendation, and want the non-exercise TDEE to match what the device says.
You got a tested RMR figure and BMR based on that is very different than BMR the device is using for non-moving time, so you want to adjust the height to try to get the BMR's the same for better TDEE estimate.
Your Katch BMR is very different than BMR device is using, desire same adjustment for better TDEE estimate.
Fill in Simple Setup tab down to the Goal Weight.
Go to FitBit_BodyMedia tab.
Enter in your tested RMR if you have one, leave blank if none.
Adjustment is shown for the device site to use, if desired.
3 options shown for the 2 methods.
MFP method - daily eating goal changes as activity changes or you log exercise calories, with deficit. Normal sync method, but using % deficit rather than block calories. Trying to minimize adjustment amounts so fewer big changes outside exercise.
TDEE method - daily eating goal changes a little bit as activity changes, no logging of exercise expected if the device correctly estimates calories burn.
TDEE method using Simple Setup tab - static daily eating goal, and device used for non-exercise day TDEE, activity calc for exercise, potentially bigger deficit given if lifting part of routine.
Strongly recommended if you used HRM calorie burns in TDEE calculations, or do lots of cardio and need to confirm you are eating enough for performance and useful to keep cardio from burning your muscle off.
How to set up your HRM for more accuracy, and if you fill in stats requested, your own personal calorie burn chart based on a Polar funded study formula that found great accuracy, enhanced with your own tested or better measured stats.
HR training zones and how they should be used.
Advice gathered for creating good workout schedules to get the most benefit from whatever you are doing.
Future You tab.
The start, and rather detailed TDEE calc. Easier to use new instructions for this method are on the Simple Setup tab, bottom-right area.
The Weekly Spot Check and Confirmation is useful to enter what you did eat, what you did burn in exercise, and confirm you are ending up above BMR on avg weekly.
Setup for Garmin that may be underestimating your VO2max stat based on all your workouts that may not all be logged on the Garmin HRM.
Happy eating and fat and weight loss!
Any changes since 12/5/12 when this post was done? You may not need a new version. I'll include visual changes I remember that could be useful, and for sure math changes or method improvements.
Can check current copy out there and view the Note on the ver. dated cell as to what has changed lately on that tab, in case it matters to you.
Info in separate post below.
Joined Nov 2011
January 7, 2013 11:59 am
Good news if you want to update spreadsheet and have a newer version for some feature you want to use, but keep a filled out Progress tab with all your past stats.
If using Excel, easy to copy a Sheet over to another Workbook, so if using Excel on your own.
If using Google Spreadsheet, do the following.
Make a Copy of new spreadsheet, giving it perhaps a date related name. Leave it open.
Go into old spreadsheet that has your stats in it already.
Right click on the Progress tab (or like HRM tab if stats there to use) and select Copy to ...
Check the box for the name of the new spreadsheet, and Select.
Notice will say Done, do not choose to open it since it already is.
Close this old spreadsheet and go to the new one.
Right click on Progress tab and Delete. (unless changes were made to the Progress tab, in which case highlight just your logged data and copy it, then paste it on the Copy tab. Now delete the old one)
Right click on Copy of Progress tab and Rename to Progress.
Any other tab like HRM, you can now look at info and write that in to new sheet tab, in this case delete the Copy sheet though.
Joined Nov 2011
March 22, 2013 9:08 pm
Updates to the spreadsheet. Some you can correct yourself, some you'll need a new sheet, some won't matter to you.
Simple Setup tab.
TDEG cell D71 - changed formula to correctly select either 2% of weight to lose method, or other methods, when both at BMR levels. New formula you can paste into existing spreadsheet in that cell - =if(A71<=A75,A71,A80)
MFP Tweak tab.
Total Daily Deficit column J49:J58 - changed formula to correctly report 0 if no activity, so weekly total deficit at bottom is correct. New formula you can paste into that range if using it - =if(I49=0,0,I49+$D$32)
Future You Tab.
Added link for Goal BF% stat to see what a good goal might be to use.
Other tabs changed wording to use the Macro tab to confirm correct amount of protein is being eaten.
Simple Setup tab.
Upper right side in instructions for Eating for Future You method - added fields to log the current weight TDEE, and the goal weight TDEG, and get the same figures given as to the amount of deficit that causes, and amount of expected weight loss.
Simple Setup and TDEE Deficit tabs.
After a couple studies reviewed on the low calorie burn of lifting, changed the multiplier for the lifting burns.
Simple Setup tab cell A60, changed ending value from 4.4 to 4.
TDEE Deficit tab cell G39, same change. Cell H39, changed value from 5.4 to 5.
Simple Setup tab.
Included the recommended macro % here, based on the same common recommendations as on the Macros tab.
Too much to list here, just copy the contents of cells C89, C90, D90 to your spreadsheet to update. You will have to move existing cells down one row to make room.
Simple Setup tab.
Automated the Eating for Future You section. Too many changes to put here. If desired to use it, must get new sheet and move stats over. Sorry. Will usually result in less deficit unless very obese, in which case it will match for a little while with current BMR being the goal.
Simple Setup and TDEE Deficit tab.
Tweaked the values from the study for the 3 different levels of cardio and another study on lifting that said 0.039 calories / lb / min. Values now are middle of the road for anyone 100 - 300 lbs with BMR from 1000 to 2400.
To much to manually update, so use method below to copy Progress tab over.
Simple Setup tab.
Visual changes to the Activity Calculator, and some wording changes elsewhere. Nothing of importance if using already, but hopefully useful for first time users with jobs more than sedentary desk jobs.
Simple Setup tab.
Added Jackson Pollack 7-site skin fold bodyfat measurements and calculator. Included in BF average if values are there.
Included the results of that calc with other calculators for tracking. Didn't include all 7 sites because you could actually lose only 2 mm, but an inch gone, so not as meaningful.
Added area on right side to enter up to 31 days of your daily eaten stats, to arrive at avg eaten daily. So now for the Progress log for eaten daily stat, you have something to put there. That way if you see the TDEG was 1600, and you ate 1700 on avg, no wonder you didn't lose as much as expected. Or you ate 1450, another reason not to lose as much.
New tab, info above.
Simple Setup, MFP Tweak, TDEE Deficit tabs.
Corrected formulas for when goal weight is reached to remove deficit math.
Simple Setup tab.
Added input for a tested RMR value, and then formula's are based on that new BMR calculated from it.
Simple Setup, Progress, FitBit_BodyMedia tabs.
Changes to Activity Calculator to make it weight based (except daily standing hrs is RMR based) instead of BMR based.
Accuracy of old method depending on how close your BMR was to reference BMR that created the multiplier.
Now weight based. So if you retain LBM and your BMR stays the same, but you weigh less, your moving activities show you correctly burning less calories now.
Progress tab has section on the far right for entering in a months worth of actual eating levels, then what you lost over that time, telling you what your better TDEE might be, and what to adjust in the Activity Calc to make that happen.
FitBit tab in Method 3 does the same thing, adjusting hours to reflect device's TDEE.
Simple Setup, FitBit_BodyMedia, TDEE Deficit tabs.
Change to Activity Calculator for studies that show women have smaller BMR/TDEE because of smaller metabolic driving organs. Took 7.5% off the sedentary TDEE.
Smaller organs, smaller RMR - http://www.ncbi.nlm.nih.gov/pubmed/20164308
Women smaller BMR/TDEE for same LBM - http://www.ncbi.nlm.nih.gov/pubmed/1522233
Posts by members, moderators and admins should not be considered medical advice and no guarantee is made against accuracy.