Of spreadsheets and results tracking and such...

GottaBurnEmAll

PAV8888 wrote: »

Oh? What's a @GottaBurnEmAll columnar pad? Are we talking dead-tree as opposed to lit pixel sheets?

Oh yeah. Old school accountant paper. It's what all the bookkeepers used back in the day. To make spreadsheets.

BBum69

PAV8888 wrote: »

For total calories consumed I use the http://www.myfitnesspal.com/reports/printable_diary report function (which now only returns a maximum of 45 days instead the 365+ it used to) and usually display a month's worth of data making sure to only select the "food diary".

I then "select all" and copy and paste "as is" into a new Excel spreadsheet with a minimum of two blank sheets (by default Excel launches with enough)

A couple of semi manual clean up macros later (and after a sanity check that I have extracted the correct number of days given that my first macro will keep circling through indefinitely if I don't pay attention) I extract the "TOTALS" lines to Sheet2 and calculate quick averages in case that's all I want!

So you log your data into MFP, then about once a month or so you import the data into your spreadsheet?

PAV8888

BBum69 wrote: »

So you log your data into MFP, then about once a month or so you import the data into your spreadsheet?

These days I've got a fairly good idea as to the average divergence of my TDEE from what my Fitbit displays and as I am more or less maintaining <desire to maintain with a slight bias to improving body comp being more important than either slight up or down tick of weight> I am content to just double check things on a once every few months basis.

As such you're correct, I pull the data off MFP and Fitbit a month at a time and import into the spreadsheets.

Previously I used to do things on a more day to day basis.

BBum69

That makes sense. I am using my spreadsheet for weight reduction, and for both tracking and predicting my progress, so it is important that I use it daily.

PAV8888

On a daily basis I don't know that any of my Excel macros would be helpful or useful to you.

My original sheet was assuming "daily" manual data entry.

Remember that weight loss is not linear and even with trending weight apps you cannot fully account for water weight and body composition changes, nor for changes due to adaptions over time.

I (obviously) think that tracking is very important given how much I play with it! But don't miss the forest for the trees. Achieving the flexibility to reach your goals over time and to limit long term backsliding is the battle; not perfect predictive ability

Christine_72

If i had even the slightest clue of how to use/copy/download/read a spreadsheet i would totally use this, as I'm sure it's awesome

I'll just have to stick with the simplistic Trendweight app.

BBum69

PAV8888 wrote: »

On a daily basis I don't know that any of my Excel macros would be helpful or useful to you.

I agree, but a conversation that includes the differences might be beneficial, if not to us, then maybe to the community. Besides, it's not like the forums are flooded with people wanting to geek out about spreadsheets, sooo...

Remember that weight loss is not linear and even with trending weight apps you cannot fully account for water weight and body composition changes, nor for changes due to adaptions over time.

In addition to the daily inputs (3) that help me to meet my "end of this week" goals, I also transfer results (2) from that table, plus my weekly weigh in, into another section that predicts long term weekly loss. When I enter my weight at the end of the week, a new BMR is calculated for the following week, as well as the expected loss and expected weight at the end of that week, with a given target TDEE. It then recalculates the same for every following week to the end of the sheet, with target TDEEs that change about every 8 weeks. Not only does this take into account that the weight loss will be non-linear, it shows how much the difference will be week to week, or month to month, or any timeframe that you want to look at. (Assuming that all of the TDEE goals in between are met.)
Comparisons between the expected and actual results are made too, so I can see instantly the amount and direction of error. The error is usually pretty small, often smaller than the resolution of my scales (0.2 lbs), but there have been a few fliers. Water weight is indeed difficult to take into account. I recently added tracking for water percentage, among other things, that aren't used in the calculations but might help pinpoint the cause of errors. There is also a calculation that converts the actual weight loss and calorie inputs into a number that represents the Harris-Benedict multiple. I use this to refine the calculations over time, which will take care of any changes due to adaption other than weight loss and age.

I (obviously) think that tracking is very important given how much I play with it! But don't miss the forest for the trees. Achieving the flexibility to reach your goals over time and to limit long term backsliding is the battle; not perfect predictive ability

The flexibility that this allows for is the best feature! First, in the short term, I spread the variance of intake and exercise over a week, which is MUCH easier than trying to do that daily! Second, by looking at what affect a small change has over the long term, I can modify my plan very subtly here and there. For example, I can change a two month period of a 1700 average TDEE target to 1750, or from 8 weeks to 7 before going to 1900, and see what that does long term. If I get a little ahead now, I can plan to be less restrictive later, either in calories or in the timeline. If it looks like I may be getting behind me goals (I'm not), I could adjust by 25 calories per day over 6 months, instead of try to make it up in a month. As far as the issue of backsliding, the sheet currently runs to the end of 2018, long after I will be in my goal range, and I can extend the sheet until the end of my lifetime with a quick cut and paste. Whether I keep up with the daily tracking after I get there or not depends on how difficult it is for me to stay in that range. I can easily add a calculation that tells me how many calories I would need to net to recover from drift if I need to, most of that math is already being done. Worst case scenario, I will just keep logging daily to maintain. That's a very small price to pay to keep my weight in check!

PAV8888

Now I am going to throw a small curve ball at you ;-)

MFP (and as best as I can tell Fitbit too) uses Mifflin-St Jeor for BMR, which is a 1990 revision of HB. So are you using the original 1918 HB, or the 1984 HB revision or the 1990 one by Mifflin ;-)

Not that you or anyone else *has* to, 'cause obviously that's not the case, but I would encourage people to put up a "no edit rights" link of an example of their spreadsheets perhaps populated with some random values both in order to make it easier to see what they're doing and to follow their discussion and for non spreadsheet inclined people <hi Christine!> to see some examples that they might start using in case they get inspired!

I expect that wanting to occasionally run the numbers will act as an extremely valuable safety precaution many years from now when logging might not be super exciting by itself any more ;-)

I don't know your starting point; but based on what I observed on myself the numbers were much more accurate in the earlier stages when there was an abundance of fat available to lose and less of a body composition component since fat was what was overwhelmingly being lost.

However especially with barely overweight or normal weight people (and also with larger deficits) the ratio of fat to lean mass changes and more lean mass is lost.

The problem is that whereas 3500 is generally agreed upon as a caloric value for 1lb of fat... the value for lean mass is much more open to question. And it may be a different value when the lean mass is lost vs when it is regained.

In any case, bio-impedance scales don't inspire when it comes to the body composition results they spit!

Machka9

PAV8888 wrote: »

Not that you or anyone else *has* to, 'cause obviously that's not the case, but I would encourage people to put up a "no edit rights" link of an example of their spreadsheets perhaps populated with some random values both in order to make it easier to see what they're doing and to follow their discussion and for non spreadsheet inclined people <hi Christine!> to see some examples that they might start using in case they get inspired!

As I said, my spreadsheets are just for exercise, and not really for weight loss purposes. They're more for my own personal interest.

MFP's food log does just fine for tracking my intake.

BBum69

PAV8888 wrote: »

Now I am going to throw a small curve ball at you ;-)

MFP (and as best as I can tell Fitbit too) uses Mifflin-St Jeor for BMR, which is a 1990 revision of HB. So are you using the original 1918 HB, or the 1984 HB revision or the 1990 one by Mifflin ;-)

I'm glad you went there, this is the stuff that makes it worth talking about! I used the original Harris-Benedict equation for BMR, but it really wouldn't matter which one I used because the multiplier that establishes the NEAT portion of the calculation is a separate variable, and is derived from an average of actual results. So, even if the BMR calculation itself is wildly inaccurate, the multiplier should correct for it in the expected weight and loss numbers.

Not that you or anyone else *has* to, 'cause obviously that's not the case, but I would encourage people to put up a "no edit rights" link of an example of their spreadsheets perhaps populated with some random values both in order to make it easier to see what they're doing and to follow their discussion and for non spreadsheet inclined people <hi Christine!> to see some examples that they might start using in case they get inspired!

I might be willing to do that, but I would have to do some clean up work first. This conversation has already given me some ideas for some things to change. It would also be preferable to let it age before releasing it into the wild.

I expect that wanting to occasionally run the numbers will act as an extremely valuable safety precaution many years from now when logging might not be super exciting by itself any more ;-)

Yes, working on the spreadsheet has been an inspiration to lose the weight, which has inspired me to work on the spreadsheet. When the spreadsheet is as far as I want to take it, and the weight has been lost, the confetti parades for logging will certainly slow a bit.

I don't know your starting point; but based on what I observed on myself the numbers were much more accurate in the earlier stages when there was an abundance of fat available to lose and less of a body composition component since fat was what was overwhelmingly being lost.

However especially with barely overweight or normal weight people (and also with larger deficits) the ratio of fat to lean mass changes and more lean mass is lost.

The problem is that whereas 3500 is generally agreed upon as a caloric value for 1lb of fat... the value for lean mass is much more open to question. And it may be a different value when the lean mass is lost vs when it is regained.

That makes sense. I noticed some of that work in your spreadsheet, and I will definitely incorporate it into mine. My "weight loss journey" ends in mid-June 2018, whether I get to my current goal or not. After that I can work on improving body composition, but my weight range will be set in stone, and maintaining it will be my priority. The change from weight loss to composition improvement calculations might extend my interest in the spreadsheet for a while longer though. Thanks for that... I think?

In any case, bio-impedance scales don't inspire when it comes to the body composition results they spit!

I agree. I make notes of the readings and do a couple of conversions from percentage to pounds, or vice versa, but none of that information goes into the calculations. I consider it interesting, but not important. Maybe when my weight is stable, and the inaccuracies are on full display, I will find them to be neither.

heybales

Prior to Fitbit I had the logged stats of results driven TDEE and BMR for the weight and personal multiplier too.

Was useful as seasons changed, just look up how it started changing, use the same multiplier, at new weight.
As you said, BMR didn't matter, as long as whatever formula could be applied to new weight with whatever multiplier to use.

I'd use that to mess around with potential schedules and eating levels needed, just to see how things might work out.

Now just use Fitbit.

jjpptt2

I've dusted off an old spreadsheet I use for calorie/macro tracking. It's not significantly different from MFP, it's just easier to use (IMO), and puts the focus on averages rather than daily numbers. It helps take the edge off my bad days when I'm focused on rolling averages rather than a particular day's numbers.