Saturday, January 21, 2017

Tracking Diet Progress Quantitatively in Excel

Weight loss time targets (or, "how am I gonna fit into my wedding dress?!")

Setting a target weight is the first part of any diet/exercise regime. Usually this is done by looking at BMI charts for your height:

BMI = [body mass (kg)]/[height in m]^2
Where a normal BMI is 20-25 for Caucasians (upper limit is 23 for Asians -- check data for your individual ethnicity).

So if you have a BMI of 30, you might make it your goal to reach BMI 25 in one year. Or six months. And you work out the number of kilograms that corresponds to, and maybe you figure out your average weekly weight loss target.
But inevitably you have to concede that maximal healthy weight loss is about 1-1.5kg/week, which limits how quickly you can fit into that wedding dress.

Most people tend to leave it there: a weight target and a vague time-based goal to reach that target. But how do you know if you're on track to meet your goal? And if your weight loss stagnates/plateaus (as it can do, for a multitude of reasons), how do you know whether you're still going to meet your long-term goal?

Mathematically-inclined people won't learn much from this post and can probably stop reading here.

But if you're not very good at Excel and want to lose weight efficiently, then I hope this post helps you out :)

The Basics

Dieting is a pretty simple idea that gets marketed to the confused masses in a variety of exciting way, to create a multibillion $/year industry containing a lot of quackery.
Ultimately it's an accounting problem. Since most people understand businesses/budgets vaguely, let's try this analogy:
  • Energy (calorie) intake = Sales injecting revenue into company
  • Energy expenditure = metabolic + exercise = Costs of company
  • Fat on patient = Balance sheet
If your target is to shrink the balance sheet (here is probably where the analogy breaks down ;) ), you are just aim to persistently have a calorie deficit. Energy expenditure > Energy intake. That's all there is to it.
On the intake end: Eat fewer calories.
On the expenditure end: Eat foods that are less efficient to metabolise, and Exercise more.

So wait, what about Atkins?

In a previous post, I talk about the Atkins diet (in *mostly* positive terms) as a means to lose weight. If you're considering a fad diet, I recommend reading that post.
The Atkins diet doesn't explicitly stipulate a calorie limit. This is part of the reason it's a good diet for me - I'm bad at self control when it comes to eating, and with work commitments I don't get to do heaps of exercise.
Basically, the functions of Atkins are to 1) decrease caloric intake (due to protein-mediated appetite suppression) and 2) decrease metabolic efficiency (because you generate most of your blood glucose through gluconeogenesis, which is endothermic).
While this diet is convenient, it's not nutritionally complete. I recommend a multivitamin and fibre supplements.

Step 1: Collect some data

You need to start a weekly calorie deficit appropriate to the amount of fat you want to lose each week.
One kg of adipose tissue (body fat tissue) contains about 7000-7500 Calories. If you want to lose 1kg per week, you need to cut at least 1000 dietary Calories per day (=1000 kcal in SI units).
Set up your dietary and exercise regime. I'm not very inventive so I do the same thing every day to avoid hassles.
Now carry on with your regime for a month. In Excel, write the date in one column (A) and your weight in kg in the another column (B). On the dates column, select the entire column by right clicking on A and clicking "Format cells". Then select "Date" as the data type. Note that dates in Excel are actually stored as numbers (a number of days since some day in the 70's when Excel was invented) but can display as dates in the usual format. We use this property later.

Add a third column for BMI, and enter the formula "=B2/(1.x^2)" where you replace 1.x with your height in metres. (B2 assumes the first row is column titles eg Date, Weight etc). Put this formula in C2. Click on C2 and then click the bottom right corner, and drag it down the column to extrapolate the formula for all future values.

Now we have 4 records of date/weight/BMI (one for each week you've entered).

If you're interested, you can add a "weekly weight loss" column in column F (say), where the formula in F3 is "=(B2-B3)/(A2-A3)/7"

Step 2: What will be my weight in the future?

Select the date and weight columns, and go Insert>Chart. Follow the prompts to make a line graph. Then right click on the chart and go "Add trend line" and select linear. This is your approximate weight trajectory. A formula should pop up (y= a*x + b), which you can copy paste into a new column D (cell D2), replacing x with the value A2. Again, click and drag this formula down the rows and you will get an approximate weight for every date in the column A. If you drag beyond your entered data, you can add in notional future dates and it will work out your future weights for you.

Example of weight vs time chart made in Excel, a line of best fit, and the generated formula

If you like, you can repeat the above process to make a BMI column, as well.

Step 3: When will I reach my target?

Now for the interesting part, we work out the date at which you reach a target kg (or BMI).
Just calculate the inverse of your kg function by swapping x and y in the formula, then solve for x. Or, just the Wolfram Alpha tool.
Then create a column with target weights you're interested in, and another column that computes the date for these weights next to it, using the formula you found.
Fairly easy to do the same thing for BMI, too!

Can I copy your Excel sheet?

Sure, I'll upload a user-friendly template later. Or comment below and I'll send it to you.

External links



No comments:

Post a Comment