Calculating best fit trends

To calculate the rate of weight loss or gain and the calorie shortfall or excess responsible for it from the moving average trend line, the Excel spreadsheet finds the straight line trend that best fits the curve traced out by the moving average by the method of least squares. The process of finding a line that accurately represents the trend of a collection of data points is called linear regression, and the least squares method is the most frequently used approach to the problem.

Any non-vertical straight line (you'd be in a fine pickle if your weight trend line were vertical, wouldn't you?) can be expressed in the form:

Y = ml + b where m is the slope, giving the change in the Y axis value for each unit change along the X axis, and b is the intercept, the point at which the line crosses the Y axis when X is zero.

To find m and b for the line that best fits a collection of data points D\, Di,..., Dn we calculate:

h = (E"=i A-)(DLi - (E"=i 0(E"=i m " «E5L i<2-(E?=i02

Since we're only interested in the rate of change, we only need the slope, m, which gives the daily rate of change in the line that best fits the moving average trend curve. From the slope, the average weight change per week is just seven times the daily change,

7 m and the average daily calorie deficit (if negative) or excess (if positive) is:

3500m

Keep Your Weight In Check During The Holidays

Keep Your Weight In Check During The Holidays

A time for giving and receiving, getting closer with the ones we love and marking the end of another year and all the eating also. We eat because the food is yummy and plentiful but we don't usually count calories at this time of year. This book will help you do just this.

Get My Free Ebook


Post a comment