Spreadsheet for bodyfat, BMR, TDEE, progress tracker
heybales
Posts: 18,842 Member
Thought I should have an official thread in this group finally to point folks too, tweaked to support this method. Spreadsheet isn't changed, but just changing some stuff up in the usage description.
Here is link, but please read below too for explanation.
https://docs.google.com/spreadsheet/ccc?key=0Amt7QBR9-c6MdGVTbGswLUUzUHNVVUlNSW9wZWloeUE
Full blown description if wanting info on other tabs and the whole BMR TDEE concept.
http://www.myfitnesspal.com/topics/show/813720-spreadsheet-bmr-tdee-deficit-macro-calcs-hrm-zones
So I didn't like the fact I could never find a website that had all the best estimates in one place it seemed. Beyond that, can't save your stats on a website, let alone log progress.
Scooby came closest, after I already created it and saw what they did. But still no logging, and no bodyfat calc.
So this spreadsheet, Simple Setup tab, may appear full, but don't be glazed over by different colors and many places for info. Half is US, half is metric measurements. Just read it actually, it's not that full.
Delete existing data in yellow cells.
5 stats you probably have memorized - gender, age, weight, height, goal weight (though a calc to help with that too).
8 measurements you may already have or should have to estimate body fat and track progress with. Optional skin-fold measurements if you have them already or will get them done.
That average body fat % is then entered in correct cell. If you have Bodpod or hydrostatic weighing or DEXA, even better, enter that.
Activity calculator that is finer detail than 5 levels normally used with time. Is 1 hr walking equal to 1 hr lifting equal to 1 hr running? No. What if your daily job is already on your feet a lot, or your 40 hrs is home chasing around kids all day, now what level?
2 levels dealing with your work time, either nothing entered because sedentary desk job is correct, or hours of time standing moving slowly during those 40 hrs, or hours moving a lot and lifting stuff too.
3 levels dealing with minutes of cardio, you equate it to walking level at different speeds, usually pretty obvious what level, though walking incline could move you up a level.
1 level for true lifting with sets and rests of normal 1-5 minutes, lower calorie burn during the session because that is true. If your lifting doesn't have rests like that, it's high cardio level then with more calorie burn.
I will suggest, if you are using FitBit or BodyMedia, (and this is being added as a tab soon to make it easier), add up 5-7 days that had NO exercise and get avg TDEE from those non-exercise days.
Now in the Activity Calculator, add hours to the service or labor trade levels to make the reported TDEE match.
Now add in your exercise time in correct levels.
Now there are Your Results.
Now, the TDEG given there is likely more than what this group suggests, because if you are lifting it gives a bigger deficit as possible, because lifting, good protein intake, and reasonable deficit, all help retain muscle mass.
But you can take the TDEE minus 15%, 10% if down to last 10 lbs.
Use the suggested macro % to confirm you are getting enough protein and fat, and you can always eat a tad more of those if desired. If lots of cardio, not too much more though as you'll need carbs.
Go to the Progress tab and delete existing info, and enter in your stats as shown, filling in your own desired TDEG.
If you'd rather eat back your exercise calories because workouts are too variable or iffy, then don't enter any exercise in the Activity Calc and only fill in the daily activity levels, and go to the MFP Tweak tab.
Enter the Katch BMR shown.
Enter the BMR multiplier shown.
Now there is your daily goal, and instructions to what to change on MFP and macros.
Now in bottom section, enter in workout session time and reported calories burned. The column for eat-back is what you log for the exercise and eat that back.
For lifting, use the MFP estimate as it is low and more correct than anything else, with time, and eat back will be lower.
So read the suggestions under Your Results no matter what method you use, but some comments only apply to TDEE Deficit method, not eat-back method.
Other suggestion if you don't have Bodpod or such for BF%, and just using the measurements.
Those 2 calcs are highly rated for about as good accuracy as you'll get with measuring, obviously can still be off, but since they mainly use different sites, they balance well. If the spread between them is over 5%, not that accurate yet, but usually becomes better as inches lost.
So have no fear if BF% seems to go up, it's just becoming more accurate. Just adjust as needed.
Enjoy seeing real progress, even if not always scale weight.
Here is link, but please read below too for explanation.
https://docs.google.com/spreadsheet/ccc?key=0Amt7QBR9-c6MdGVTbGswLUUzUHNVVUlNSW9wZWloeUE
Full blown description if wanting info on other tabs and the whole BMR TDEE concept.
http://www.myfitnesspal.com/topics/show/813720-spreadsheet-bmr-tdee-deficit-macro-calcs-hrm-zones
So I didn't like the fact I could never find a website that had all the best estimates in one place it seemed. Beyond that, can't save your stats on a website, let alone log progress.
Scooby came closest, after I already created it and saw what they did. But still no logging, and no bodyfat calc.
So this spreadsheet, Simple Setup tab, may appear full, but don't be glazed over by different colors and many places for info. Half is US, half is metric measurements. Just read it actually, it's not that full.
Delete existing data in yellow cells.
5 stats you probably have memorized - gender, age, weight, height, goal weight (though a calc to help with that too).
8 measurements you may already have or should have to estimate body fat and track progress with. Optional skin-fold measurements if you have them already or will get them done.
That average body fat % is then entered in correct cell. If you have Bodpod or hydrostatic weighing or DEXA, even better, enter that.
Activity calculator that is finer detail than 5 levels normally used with time. Is 1 hr walking equal to 1 hr lifting equal to 1 hr running? No. What if your daily job is already on your feet a lot, or your 40 hrs is home chasing around kids all day, now what level?
2 levels dealing with your work time, either nothing entered because sedentary desk job is correct, or hours of time standing moving slowly during those 40 hrs, or hours moving a lot and lifting stuff too.
3 levels dealing with minutes of cardio, you equate it to walking level at different speeds, usually pretty obvious what level, though walking incline could move you up a level.
1 level for true lifting with sets and rests of normal 1-5 minutes, lower calorie burn during the session because that is true. If your lifting doesn't have rests like that, it's high cardio level then with more calorie burn.
I will suggest, if you are using FitBit or BodyMedia, (and this is being added as a tab soon to make it easier), add up 5-7 days that had NO exercise and get avg TDEE from those non-exercise days.
Now in the Activity Calculator, add hours to the service or labor trade levels to make the reported TDEE match.
Now add in your exercise time in correct levels.
Now there are Your Results.
Now, the TDEG given there is likely more than what this group suggests, because if you are lifting it gives a bigger deficit as possible, because lifting, good protein intake, and reasonable deficit, all help retain muscle mass.
But you can take the TDEE minus 15%, 10% if down to last 10 lbs.
Use the suggested macro % to confirm you are getting enough protein and fat, and you can always eat a tad more of those if desired. If lots of cardio, not too much more though as you'll need carbs.
Go to the Progress tab and delete existing info, and enter in your stats as shown, filling in your own desired TDEG.
If you'd rather eat back your exercise calories because workouts are too variable or iffy, then don't enter any exercise in the Activity Calc and only fill in the daily activity levels, and go to the MFP Tweak tab.
Enter the Katch BMR shown.
Enter the BMR multiplier shown.
Now there is your daily goal, and instructions to what to change on MFP and macros.
Now in bottom section, enter in workout session time and reported calories burned. The column for eat-back is what you log for the exercise and eat that back.
For lifting, use the MFP estimate as it is low and more correct than anything else, with time, and eat back will be lower.
So read the suggestions under Your Results no matter what method you use, but some comments only apply to TDEE Deficit method, not eat-back method.
Other suggestion if you don't have Bodpod or such for BF%, and just using the measurements.
Those 2 calcs are highly rated for about as good accuracy as you'll get with measuring, obviously can still be off, but since they mainly use different sites, they balance well. If the spread between them is over 5%, not that accurate yet, but usually becomes better as inches lost.
So have no fear if BF% seems to go up, it's just becoming more accurate. Just adjust as needed.
Enjoy seeing real progress, even if not always scale weight.
0
Replies
-
Bump0
-
Bumpin for later. thanks Haybales!0
-
Bump0
-
Thanks! Will save to read later.0
-
bump! :-)0
-
Bump! Could this be a "sticky" thingy...?0
-
bump0
-
Thank you for this
x 0 -
bump for later0
-
Bump
0 -
Thank you for your detailed post.0
-
Thanks bumping for later 0
-
bump0
-
bump0
-
Bumping to read later...0
-
bump0
-
@ Heybales... Off topic a bit but .I just performed your HRM test to see how accurate mine was and thought I'd let you know compared to fitbit for the 20 mins. My HRM gave me 118 cals when I put it into the web link it gave me 134cals and fitbit gave me 135 cals. Close enough for me.
So now I'm wondering if I should add 13.5% onto my spin burns that I use my hrm for ?????? I seriously dont want to over think it as I've spent many hours doing that. It would give me 30-60 cals extra but I only do that once a week so its not going to make much difference. But maybe my weights ....and yes I know its not the best way.
Strength training on mfp is close to fitbit burn but my HRM is quite a bit more so maybe that would take some afterburn into account for me.......mmmmmmm
Thanks as always0 -
@ Heybales... Off topic a bit but .I just performed your HRM test to see how accurate mine was and thought I'd let you know compared to fitbit for the 20 mins. My HRM gave me 118 cals when I put it into the web link it gave me 134cals and fitbit gave me 135 cals. Close enough for me.
So now I'm wondering if I should add 13.5% onto my spin burns that I use my hrm for ?????? I seriously dont want to over think it as I've spent many hours doing that. It would give me 30-60 cals extra but I only do that once a week so its not going to make much difference. But maybe my weights ....and yes I know its not the best way.
Strength training on mfp is close to fitbit burn but my HRM is quite a bit more so maybe that would take some afterburn into account for me.......mmmmmmm
Thanks as always
And that's why FitBit is really good on walking and running, using the calculations really, weight and pace. Pace based on steps and stride length.
But not strength training, lucky it matches so close. Don't use HRM, do you really want to eat back the afterburn which is fat calories? That's the free extra burn from increased metabolism, you don't want to eat that back.
Usually any difference with HRM becomes more inaccurate as HR goes up. So what was 13.5% at walking HR level, becomes 30% at spin bike level. Unless, you do the spin bike at the same avg HR as you did the walking.
Other way to test. Do your next spin class and note your avg HR you obtained. Should always note that anyway.
Now on your next walking day on treadmill, during your warmup get the speed and incline up to hit that same avg HR. Now hold it for 20 min, lowering incline if needed to maintain that avg HR.
Then take that avg incline and speed used for that 20 min to the calculator, and there's your calorie burn for 20 min at that HR. Now you can see how far off the HRM is at that level.
So walking was only 48 calories lower on the HRM for an hour. Not bad really, seen worse. And higher HR likely would be worse.0 -
Thanks for the tip re spin & weights. I will re-do the exercise this week with the spin hr. I'm interested to see the difference as my average hr will definitely be higher than today.0
-
Major new tab added - FitBit_BodyMedia
So reason for using this rather than default MFP syncing and using.
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.
Delete sample yellow cells.
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.
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.
I can still make improvements to it, so feedback still desired.0 -
bump for later0
-
Bump0
-
So I retested with my average heart rate for an half hour spin. Average heart rate was 157 which was pretty hard to do not going over the speed which I think you said was about 6.4km(I could be wrong as I cant for the life of me find the original thread) So I walked at 6kmph at 12 incline.
HRM 190
Calc 221
fitbit 145
So this time compared to the walk/run link it was close to 20% under. One weird thing was that the fitbit registered me walking at 6.6km.
I wondered if the high incline may have put it out too much? Would you suggest I retest at a higher speed and lower incline. But I only have little short legs so it will end up a jog.
Thanks0 -
So I retested with my average heart rate for an half hour spin. Average heart rate was 157 which was pretty hard to do not going over the speed which I think you said was about 6.4km(I could be wrong as I cant for the life of me find the original thread) So I walked at 6kmph at 12 incline.
HRM 190
Calc 221
fitbit 145
So this time compared to the walk/run link it was close to 20% under. One weird thing was that the fitbit registered me walking at 6.6km.
I wondered if the high incline may have put it out too much? Would you suggest I retest at a higher speed and lower incline. But I only have little short legs so it will end up a jog.
Thanks
Very interesting. Good memory too.
Max was 6.3 mph actually jogging flat, so 10 Kmph.
Walking is max 4 mph, so 6.4 Kmph.
12% incline probably too steep, as personal efficiency starts making a big difference. You can walk steep emphasizing the glutes or the quads, or balance between them. Or very inefficient with big steps, or way too small steps.
Probably 5% is max incline.
But jogging up to 10 Kmph should probably allow getting your HR up to matching 157. Just need a longer warmup then.
And in the studies, up to that speed the calculations were still within 15 calories of tested.0 -
wow thanks for listing that s/s! This is exciting.0
-
Is this bf calc more accurate?0
-
Is this bf calc more accurate?
If you dig around studies on those 2 calcs, those are usually considered the two best. What's also nice is they mainly use different body parts. So out of proportion in one area can have some balance by the other formula at least.
Though, say even at goal weight in years past, if you have small wrists compared to say forearms bigger, or big thighs compared to smaller hips - your body can throw them off badly in mainly 1 direction.
Usually they get throw off in opposite directions, so the avg still is within 5% of reality. Especially as inches are lost and accuracy improves.0 -
So I retested with my average heart rate for an half hour spin. Average heart rate was 157 which was pretty hard to do not going over the speed which I think you said was about 6.4km(I could be wrong as I cant for the life of me find the original thread) So I walked at 6kmph at 12 incline.
HRM 190
Calc 221
fitbit 145
So this time compared to the walk/run link it was close to 20% under. One weird thing was that the fitbit registered me walking at 6.6km.
I wondered if the high incline may have put it out too much? Would you suggest I retest at a higher speed and lower incline. But I only have little short legs so it will end up a jog.
Thanks
Very interesting. Good memory too.
Max was 6.3 mph actually jogging flat, so 10 Kmph.
Walking is max 4 mph, so 6.4 Kmph.
12% incline probably too steep, as personal efficiency starts making a big difference. You can walk steep emphasizing the glutes or the quads, or balance between them. Or very inefficient with big steps, or way too small steps.
Probably 5% is max incline.
But jogging up to 10 Kmph should probably allow getting your HR up to matching 157. Just need a longer warmup then.
And in the studies, up to that speed the calculations were still within 15 calories of tested.
So the guinea pig is back again.
Did it this time at 8kmphr incline 3... It was bang on for my 157 average hr
Results
Exrx 212
Hrm 184 (202 if I take into the 13.5% from previous test.
Fitbit 231
So pretty close to the 15 cal difference you mentioned earlier.
Thanks for the information. I've been going through your spreadsheet and it seems the only real change I need to make is change active activity to very active. I average 15,000 steps a day and at least once a week 20,000. I wish more mums would realise that it is not sedentary activity being at home a lot. So I'm eating at active tdee for a month then will go from there. Bump to small cut at very active will probably equate to similar daily cals....maybe 100 more.
Thank you for your time0 -
bump0
-
bump0
