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?

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!