Week 11 - I have new stats to track!

Unable to leave a good thing alone, I made another tweak to my existing spreadsheet. I'm now tracking BMR/TDEE and expected losses. Obviously this isn't totally accurate, as I'm not 100 percent perfect in tracking my food,  but it does make my graph a little more interesting. Also, this week I'll be discussing my spreadsheet itself a little bit, as well as the data that I'm collecting.

So, let's hit the detail page first!

Yeah, you'll want to click this if you want to actually read it.

So, 2.2 pounds down for the week, and fairly heavy exercise. I'll take it. But you've seen this before, so let's talk about how this sheet works.

• Date, day, weight: These three fields are static. I was able to copypasta the Date and Day to fill them, and weight is entered manually every day. Basic stuff.

• BMI: this field is interesting. First, it's a formula. It grabs today's weight and divides it by my height in inches (I have that number stored on a separate stats page), and multiplies by 703. The formula itself looks like this: =(C90/(Stats!$A$2^2))*703. This generates the number, and then I have a conditional formatting on the fields that changes the color. It started as a brick red (over 40), and will be Red until it dips below 35, at which point it will change to yellow.

• Notes: manually filled.

• Loss Today, Total Loss, Avg Loss/Day, Avg Loss/Week, Total Loss/Week: All of these are calculated fields based on today's weight compared to historic weights. Here's what the formulas look like:

• Loss today: =C89-C90 

• Total Loss: =$C$2-C90

• Avg Loss/Day: =G90/K90

• Avg Loss/Week: =G90/11

• Total Loss/Week: =C82-C90

• Days on Plan: Pre-filled.

• Exercise Calories, food Calories, Carbs, Fat, Protein, Sodium: I enter all of these manually each day

• Net calories: Calculated with a basic formula (=N89-M89)

• Weekly Net calories: a slightly less basic formula: =SUM(O82:O89). For this formula, I'm using Thursday as my start day and Wednesday as my ending day, because I enter weight in the AM but Calories at night. That gets me a full week of data.

This sheet does all of the heavy lifting, and from it I build my weekly tracker, my graph, and my new sheet. You'll see the new sheet soon, but first let's hit my weekly weight loss progress table.

click  me!

I got to tick off a couple of boxes this week, and I'm moving closer to goal! I won't go into as much detail on this, but I am pulling the Weight number from my first detail sheet and calculating all the averages based on starting weight and the current week. Colors/highlighting is all done manually because honestly it's easier for me.

Now... on to the new sheet!

Ooh! New sheet!

This sheet keeps track of weight loss by week (pulling the week and actual weight from my weekly tracker sheet, above). It calculates my actual loss, and then also calculates my BMR using the Harris-Benedict Principle (formula: =66+(6.23*B2) + (12.7*Stats!$A$2)-(6.8*Stats!$B$2))

I'm grabbing the weight from the sheet, and my height and age from my stats sheet. Then I calculate my TDEE by taking that and multiplying by 1.2 (for my Sedentary desk job). I multiply by 7 to get my weekly TDEE (yeah, I'm abstracting this to the week and not doing daily calculations), then I grab my Weekly net calories from my first page. I do a quick subtraction to get my deficit. I count my TDEE as sedentary since my FitBit and MFP are tracking my calorie burn. From this, I get an expected loss per week (deficit/3500), and an Expected Weight.

I'm unsure how accurate this will ever be, since my calories burned/consumed are all abstractions, and the H-B method isn't without flaws either. That said, it's a good baseline.

I did all this and it made my graph more interesting!

Look! TWO lines now!

So now I am tracking not only my actual weight, but my expected weight based on Calories In vs Out. You can plainly see the extra loss in the first couple weeks, where I'm dropping weight super fast as water falls off, and then a hiccup in week 7, when I held onto a ton of extra water, and now the lines are moving nicely in step. I'm a little above my expected, but I'm not surprised, as I expect my calories burned is over estimated by MFP and my calories eaten is almost certainly a bit too low since I don't track every condiment and add-on. As long as the two lines are keeping fairly close though, then I know I'm accurate enough.

And finally, I hit level 10 in HabitRPG and became a Rogue! Woo? Woo!