Of spreadsheets and results tracking and such...

PAV8888

Other than @heybales (whom I consider THE original purveyor of MFP spreadsheets), and @EvgeniZyntx (who hasn't been around much this past year), do we have any interesting (semi) automated spreadsheet users in this crowd?

I used to use the following spreadsheet to track my trending and scale weight results against my calories in and calories out logging. It displays the results as a % of TDEE error.
https://docs.google.com/spreadsheets/d/1VDmqNpLPu7sbQSochUJNXdp2F7AN15AGgkvS3zLw1GU/edit?usp=sharing (nope, the sample data are not my own ;-)

These days I am more curious about my total calories consumed.

Even non-pay Fitbit users such as myself can use https://www.fitbit.com/export/user/data while logged in to their fitbit accounts to get a month's worth of reports at a time and compare them to their corresponding http://www.trendweight.com and scale weight results.

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!

But, more often than not, I then select all the daily result lines in Sheet2 and paste them to the right of the appropriate starting date in this google sheet: https://docs.google.com/spreadsheets/d/1vwjCcATCzhU1IFTufaM2OphSqLsGbJqQxNHYpch43L8/edit?usp=sharing

Any "gimme's" on improvement would, of course, be very welcome indeed! In the meanwhile, based on my currently rudimentary Excel macro skills, these are the clean-up macros I've come up with:

Sub MFP()
'
' MFP Macro
'
' Keyboard Shortcut: Ctrl+t
'
Cells.Find(What:="TOTAL:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Rows(ActiveCell.Row).Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Selection.Offset(1, 0).Select
Sheets("Sheet1").Select
Selection.Offset(1, 0).Select
End Sub

AND

Sub MFP_Second_Clean()
'
' MFP_Second_Clean Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Range("A1").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:I5000").Select
Selection.Replace What:="g", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="m", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[1]C:R[5001]C)"
Range("B1").Select
Selection.Copy
Range("C1:I1").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "AVERAGE"
Range("B1").Select
End Sub

Anyway, would love to see other people sharing their own spreadsheets and/or suggesting how to implement improvements!

**and thanks to this thread I discovered an incorrect September 7 entry! One that had increased my carbs for the day by about 4,000g, giving me THE re-feed that never was!

Nony_Mouse

Cool title (needs more dragons?)

My current (just generated) spreadsheets are just the Fitbit dump (thank you so much for pointing out to me how to do that!! ), deficit column added, with cals in, cals out and deficit totalled for the month, then anticipated weight loss according to deficit, actual scale weight loss, and Trendweight loss. That's about as much as my wee brain can handle right now, but it was an awesome distraction, and omg so much easier than the scribbles on paper I did earlier this week!

I shall be watching this thread for further data crunching advice

jjpptt2

Not to hijack, but does anyone have a good formula for calculating plates per side for a workout spreadsheet? I've done it in the past with a ridiculously complicated nested if, but there's got to be a better way.

TIA.

PAV8888

Not a hijack as I don't see much life in the thread so far ;-)

As mentioned my spreadsheet foo is limited
And am not 100% sure I understand the question.
So I guess it would help to see an example with maybe a shared link?

Unknown

This content has been removed.

jjpptt2

Below is my program. I reference this from my phone during my workouts. If my current set calls for if my set calls for 8 reps @ 205lbs, most of the time it's not hard to do the math in my head to figure how what plates to put on the bar. But occasionally the synapses don't fire correctly and it would be nice to have a crutch built into the spreadsheet that told me: if using a 45lb oly bar, I'd need: 45, 25, 10.

PAV8888

My thinking would result in ridiculously complicated too

Take number and subtract bar weight.
Divide total by 2 to get a side
Say we've been left after the weight of the bar with 225 /2 = 112.5 per side.

You would have the plate sized in a known location and you would use the =Quotient function against the largest plate size (say a 45lb plate). If the =quotient size is non zero, as in this case where it is 2, I would then do a =MOD and take that 22.5 result and perform another =quotient with the next lower size of plate.
I would get 0@45, 0@30, 0@25, 2@10 with a =MOD of 2.5
Then I would take that 2.5 result and do the same thing coming up with 1@2.5

So there you go: 2@45, 2@10, and 1@2.5

heybales

I created one mainly for printing out a chart, column for each plate size, says how many to use, based on what I have at home for plates.

Since at home taping to wall is no problem, along with sheet for working weights and few common warmup weights to use.

A Google sheets version could be pulled up, and likely incorporated.
But it's exactly like you mentioned, just nested equations in each column, refering to place size. I didn't even make field for how many of each, it's badly hard coded into formula.
I was going for quick and dirty and my setup only.

PAV8888

Ha: https://en.m.wikipedia.org/wiki/Knapsack_problem

Nony_Mouse

PAV8888 wrote: »

Ha: https://en.m.wikipedia.org/wiki/Knapsack_problem

Ha, I totally have a knapsack problem! I fill that poor wee thing to breaking point on winter hikes when I need to carry more (extra layers etc). Really, really must investigate bigger ones. The version I have (which I love), that did come in a variety of capacities, is no longer available

Nony_Mouse

Bump!!

C'mon guys, others must be doing geeky things, right? Or be interested in doing geeky things? Or wonder what the benefits of doing geeky things are?

PAV8888

I think this thread needs more than 95% introverts and all the participants so far are 93% or lower

I suspect that people who are really good at this stuff don't want to associate with us amateur riff raff since we would pester them with never-ending questions and requests for examples

My main reasons for posting were:

*We often tell people to evaluate their progress over time. I suspect that a lot of people have the ability to do that in a huge amount of detail while others are let down by their ability to analyze their data.

*evgeny's spreadsheet which represents a huge amount of work and research is no longer working after mfp's more recent changes.

*I was somewhat curious to see what other people are using for their own spreadsheets, especially among those who are able to analyze their data even more than I can my own

*Sometimes you get good ideas when you see other people's stuff. @amusedmonkey has posted in the past a simple spreadsheet where she just keeps track of how much above or below her total target calories she is for the week and the month. Sort of like a caloric checkbook. Even though I don't do that, I still thought it was a great idea for if I ever felt the need to be more vigilant or aware. Just as an example.

And many times I've caught glimpses of people who I'm sure analyze and fit their data in ways that I can barely follow

sgt1372

I use Excel to log my weight, cals and macros daily.

I just use simple averages and correlations to track performance. Don't even bother trying to build charts or graphs.

Pretty rudimentary and certainly not at the level of sophistication illustrated above, which is WAY beyond my self-taught spreadsheet capabilities.

GottaBurnEmAll

I am too old fashioned for spread sheets. Give me columnar paper, a pencil, and a calculator, and I'm a happy woman.

*obligatory introvert checking in here.

cmriverside

I'd show you mine, but then you'd see all my notes and I'm not gonna share that!

It's evolved over years, though. I do that Running over/under variance thing and daily and weekly averages. Also gross and net calories and exercise calories and weight and blah blah blah.

\m/ introvert obsessive types.


...and where is @EvgeniZyntx

::waves::

Machka9

I have two spreadsheets to track my exercise. One is just for cycling and I've been maintaining it since 2000 (it was paper before that). The other is for all exercise and I've been using it since Feb 2015.


I'm also into database work at work and uni and I do debate whether converting everything over to a database would be worth it.

Nikion901

I 've got a spreadsheet for my weekly weight that goes back to 2010. After I joined MFP I expanded on it to include my daily calories and macros, then added a sheet for Activities (exercise plus) and along the way I made another sheet that captures the numbers to compress into monthly totals and averages.

Interestingly, to me, I've discovered that my macros stay within a narrow % regardless of how many calories I consume.

ZoneFive

I wish I could take advantage of all these spreadsheets and graphs, but I'll be whimpering under my desk in short order if I try again. Sticking with my Word-generated tables, or GottaBurnEmAll's columnar pads.

PAV8888

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

ZoneFive

Even if the dead-tree columnar pads were under an on-fleek lamp, they wouldn't be lit enough for this brain.

Honestly when it comes to Excel I might as well be working with a Flintstones typewriter, with a bird chipping out letters on a stone slab.

PAV8888

Sounds like you might be onto a retro avant garde solution such as using the outside of a filet-o-fish box to log the weight of the tri-colour coleslaw mix and cherry tomatoes that would be eaten in close proximity... not that I would know anyone who might do something **kitten** like that!

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.