For the nerds in you! TDEE estimation, with graphs :)
nelinelineli
Posts: 330 Member
Hi there again!
I'm a hopeless data lover - I believe the best way to deal with a problem is to study and understand it, because then you can control it, and I've been approaching weight loss the same way - by weighing myself daily and keeping an accurate journal (to the best of my abilities).
I've already posted some graphs on daily weight fluctuations, plateaus etc., and if you're still upset with your scales or get discouraged by what they show, you might want to check that post: http://www.myfitnesspal.com/topics/show/1098806-newbie-loss-plateaus-and-weight-loss-math-with-graphs
Lately I've been trying to avoid work, so I turned to my weight loss data again, and produced a lot of very nerdy plots related to TDEE estimation from actual data. Hopefully it may give some of you ideas on how to kill your time, or help you figure out what's wrong if things don't work as well as planned.
So here we go. I have this excel file that looks like this:
Stuff in boldface is entered manually (daily weigh-ins, calories in, calories out, workout duration), while the rest is automatically calculated as follows:
1. Net calories = calories in - calories out
2. BMR (I used Mifflin St Jeor formula, you're free to put whichever you like) = 10*(weight in kg) + 6.25*(height in cm)-5*age+s, where s=-161 for women and +5 for men. The weight there is actually the "Expected Real weight" column defined lower. This is to compensate for the fact that BMR drops in time, as we lose weight.
3. Net Corrected = Net calories + [BMR column]/1440*[Workout duration column], where 1440 is the minutes in a day.
(This is basically to subtract the equivalent of BMR for the time of your workouts, as HMRs / MFP estimates do not do so, and you're calculating your BMR calories during workout twice)
4. TDEE sedentary (without workout) = [BMR column] * TDEE coefficient cell (which is what we're trying to calculate, I started with the default of 120%, corresponding to "sedentary")
5. Deficit = TDEE sedentary - Net Corrected
6. Expected weight loss = Deficit/7700, where 7700 is the amount of calories needed to lose 1 kg of fat
7. Expected "real" weight = (expected "real" weight of the previous day) - (expected weight loss of the previous day). The first value in this column is set manually, and as a default I took the initial weight the normal linear trend (right click -> add trendline) indicates (another small thing we can try to estimate!)
8. Expected "scale" weight = Expected "real" weight + c, where c is a constant estimating average amount of weight in your stomach. This one is really just for crazy nerds like me:P
With this setup, we can already try to see if our expected loss matches our real loss:
By varying the TDEE coefficient we can obtain several expected weight estimates. Once you hit a line that sort of fits - that's about it!
But if you're nitpicky like me, or you haven't been very consistent with your net calories (you can "identify" that by looking at how "straight" the estimated "real" weight line is - mine for example is straight enough), then you can go further and get the ACTUAL relationship between expected weight and real weight, by generating an XY scatterplot:
Add a trendline, go to "Format trendline" and tick "display equation" and "R value".
Now what you want is to get as close as possible to y(actual weight)=x(expected weight). If y > 1x+…, decrease the TDEE coeff, if y<1x+…, increase it. Play around until it hits y=1x+… If the coefficients get close but the "real" weight starts being consistently too high (or too low), readjust the initial "real" weight, to line it up as nicely as possible.
Finally, if you're really bored, you can calculate the expected "Scale" weight (what you're likely to see on the scales, not the weight of your empty body) by playing with the coefficient in column 8 until you hit y=1x+0, like this:
And here's how they look on the initial graph:
There. Now I need to get back to do some real work!
I'm a hopeless data lover - I believe the best way to deal with a problem is to study and understand it, because then you can control it, and I've been approaching weight loss the same way - by weighing myself daily and keeping an accurate journal (to the best of my abilities).
I've already posted some graphs on daily weight fluctuations, plateaus etc., and if you're still upset with your scales or get discouraged by what they show, you might want to check that post: http://www.myfitnesspal.com/topics/show/1098806-newbie-loss-plateaus-and-weight-loss-math-with-graphs
Lately I've been trying to avoid work, so I turned to my weight loss data again, and produced a lot of very nerdy plots related to TDEE estimation from actual data. Hopefully it may give some of you ideas on how to kill your time, or help you figure out what's wrong if things don't work as well as planned.
So here we go. I have this excel file that looks like this:
Stuff in boldface is entered manually (daily weigh-ins, calories in, calories out, workout duration), while the rest is automatically calculated as follows:
1. Net calories = calories in - calories out
2. BMR (I used Mifflin St Jeor formula, you're free to put whichever you like) = 10*(weight in kg) + 6.25*(height in cm)-5*age+s, where s=-161 for women and +5 for men. The weight there is actually the "Expected Real weight" column defined lower. This is to compensate for the fact that BMR drops in time, as we lose weight.
3. Net Corrected = Net calories + [BMR column]/1440*[Workout duration column], where 1440 is the minutes in a day.
(This is basically to subtract the equivalent of BMR for the time of your workouts, as HMRs / MFP estimates do not do so, and you're calculating your BMR calories during workout twice)
4. TDEE sedentary (without workout) = [BMR column] * TDEE coefficient cell (which is what we're trying to calculate, I started with the default of 120%, corresponding to "sedentary")
5. Deficit = TDEE sedentary - Net Corrected
6. Expected weight loss = Deficit/7700, where 7700 is the amount of calories needed to lose 1 kg of fat
7. Expected "real" weight = (expected "real" weight of the previous day) - (expected weight loss of the previous day). The first value in this column is set manually, and as a default I took the initial weight the normal linear trend (right click -> add trendline) indicates (another small thing we can try to estimate!)
8. Expected "scale" weight = Expected "real" weight + c, where c is a constant estimating average amount of weight in your stomach. This one is really just for crazy nerds like me:P
With this setup, we can already try to see if our expected loss matches our real loss:
By varying the TDEE coefficient we can obtain several expected weight estimates. Once you hit a line that sort of fits - that's about it!
But if you're nitpicky like me, or you haven't been very consistent with your net calories (you can "identify" that by looking at how "straight" the estimated "real" weight line is - mine for example is straight enough), then you can go further and get the ACTUAL relationship between expected weight and real weight, by generating an XY scatterplot:
Add a trendline, go to "Format trendline" and tick "display equation" and "R value".
Now what you want is to get as close as possible to y(actual weight)=x(expected weight). If y > 1x+…, decrease the TDEE coeff, if y<1x+…, increase it. Play around until it hits y=1x+… If the coefficients get close but the "real" weight starts being consistently too high (or too low), readjust the initial "real" weight, to line it up as nicely as possible.
Finally, if you're really bored, you can calculate the expected "Scale" weight (what you're likely to see on the scales, not the weight of your empty body) by playing with the coefficient in column 8 until you hit y=1x+0, like this:
And here's how they look on the initial graph:
There. Now I need to get back to do some real work!
0
Replies
-
The geek in me is overjoyed!! :happy: Thanks for sharing this!! :drinker:0
-
BUMP!! You're seriously the best.0
-
Ohhhh, data!
OK, I think I'm now ready to go figure out my own BMR and TDEE. Thank you!0 -
Yes! More of this! I have bookmarked your previous post and use it frequently for people experiencing 'plateaus'. :drinker:0
-
This is just so awesome! I love numbers!!! :drinker:0
-
I've uploaded my excel file, if anyone is interested in giving it a try. In order to see the graphs, you need to download the file and open it with MS Excel / LibraOffice.
https://docs.google.com/file/d/0BzJL1pqFDyG4cnRiRGduQWM5Y3M/edit?usp=sharing0 -
i don't understand half of this, but this is awesome.
Go you! 0 -
oh noes! you're addicted to the scale!
*chuckle*
good stuff! I loves me some data.0 -
this is awesome.. love me some data 0
-
oh noes! you're addicted to the scale!
*chuckle*
good stuff! I loves me some data.
Haha, oh I'm a true MFP heretic! I also eat below my BMR and net below 1200.
Should I hide from the crowds that cry "burn the witch" ?0 -
Love it!0
-
This is beautiful!
Thank you!
Edit:
Uh, I'm not very well versed in Excel, if I wanted to use your document thing as a template, I figure it'd be pretty easy to get rid of your data in the columns with bold type, but would it be possible to use an earlier starting date/later ending date and stuff???0 -
Great posts!! Last one lead me here. Love em all! I will download it and try to put in my info If I can.
I go a week with just a little movement, and am told I need to eat more. I think a Plato needs to be longer than a week. :ohwell:
In the end, I'm still kickin butt!!
I only started in May. 0 -
I think I just fell in love with your brain.0
-
You are seriously my type of woman. Going to fire up excel and start measuring as soon as the sun comes up. Thank you for an inspiring post!0
-
Haha, oh I'm a true MFP heretic! I also eat below my BMR and net below 1200.
Should I hide from the crowds that cry "burn the witch" ?
Lol, yeah! Burn me too!
Seriously, nowadays I just flush any friends who start questioning my plan. Got tired of trying to 'splain. At 72, shouldn't have to explain anything!0 -
This is beautiful!
Thank you!
Edit:
Uh, I'm not very well versed in Excel, if I wanted to use your document thing as a template, I figure it'd be pretty easy to get rid of your data in the columns with bold type, but would it be possible to use an earlier starting date/later ending date and stuff???
Yes you can change the date as well, just insert your initial date in the first row and then drag the column down. Excel will automatically complete it
If you have any issues feel free to ask me I can help you set it up.1 -
I am overjoyed, there is never enough data, however I still refuse to get on the scales the day after I had pizza :-)0
-
Bump. You made the nerd in me happy with your charts and calculations. May have to try and set up my own spreadsheet.
You can download mine to get you started. Link just few posts up:)0 -
I am overjoyed, there is never enough data, however I still refuse to get on the scales the day after I had pizza :-)
Haha, Well, I had 5 pizzas over that time, and only one bumped my weight up. 0 -
I think i'm in love!! haha love getting my geek on!0
-
Bump to read later.0
-
Bump0
-
You are awesome in your awesomeness....
I too am a data nerd in my day job and came to a similar plan as yours the more data I'm collecting the better for me (I am also doing ww so tracking points and calories)
But I have yet to conquer the scales in an analytical way (ie to much of a wuss to weigh every day)... And I've only just begun.
Love your work!0 -
Wow - you are SUCH a geek. That makes me a little frisky!
Now i have to go apply your calculations to the several spreadsheets of data I have collected...0 -
The accountant and financial analyst in me is loving this...0
-
I wish I had the Excel skills to do this. This is amazing.0
-
lol i love this! MFP should have something similar to this so that everyone can log in their daily weight and make their own graphs. It would help with motivation and stop us from panicking when we do put on some weight.0
-
As many people mentioned before, there are very good tools such as trendweight and libra which you could use to visualize your weight trend!
All you have to do is enter daily weigh-ins and it will calculate stuff for you, make a nice, pretty plot:P
I'm going to quote someone from another post:I think graphing is the absolute best way to get real info out of the scale. I don't do it the way the OP has - too much work for me! - but instead, I use trendweight.com on the web or Libra on android. Trendweight looks like this:
The beauty of this is that the moving average lets you find out what your calorie deficit is and use that to plan your weight loss.
Osric0 -
Bump to check out later. I actually wanted to find out my Tdee , and i think your spreadsheet will help me out immensely. Thanks for sharing! :flowerforyou:0
Categories
- All Categories
- 1.4M Health, Wellness and Goals
- 393.6K Introduce Yourself
- 43.8K Getting Started
- 260.3K Health and Weight Loss
- 175.9K Food and Nutrition
- 47.5K Recipes
- 232.5K Fitness and Exercise
- 431 Sleep, Mindfulness and Overall Wellness
- 6.5K Goal: Maintaining Weight
- 8.6K Goal: Gaining Weight and Body Building
- 153K Motivation and Support
- 8K Challenges
- 1.3K Debate Club
- 96.3K Chit-Chat
- 2.5K Fun and Games
- 3.8K MyFitnessPal Information
- 24 News and Announcements
- 1.1K Feature Suggestions and Ideas
- 2.6K MyFitnessPal Tech Support Questions