Calculating a Trend

Your spreadsheet holds a column of dates, weekdays, weights, and a column of differences.

We would like to spot the trend in our weight loss.

Make a fresh column heading "Trend" at the top of the next available column.

Then drop down FIVE cells.

We will key in a simple formula that calculates the AVERAGE amongst five adjacent cells

Your cell pointer is resting in the empty cell where you would like to see displayed the average weight over the past five days.

Which days?

At this stage I would like you to say out loud "The average between", and then point, with your finger tip, touching the screen, to the very first cell in which you keyed in a weight value.

In my example above it is the cell immediately below the "Weight" label, and the cell holds the value "161".

As you point with your finger to that cell, say "This".

Then point to the cell immediately to the left of the fifth cell down (in my example "163") and say "and this".

"The average between this (points) and this (points)".

If we were explaining things to another human, that's what we would do, to state our intentions.

We do the same thing with the spreadsheet, except that the spreadsheet doesn't (yet) take voice or touch commands, so we will use the arrow keys.

Your cell pointer is still resting in the empty cell where you would like to see displayed the average in weight over the past five days.

Start a formula by tapping the equals key (near the top-right corner of the main block of keys on your keyboard), and then key in the word "average", without the quotes, then a left-parenthesis (Shift-nine)

Mid-construction your spreadsheet will look like this:

Now tap the left-arrow key, just once, and the up-arrow key five times to point to your first data cell ("161")

Tap the period (or "full-stop") key.

Use the down-arrow key to point to the cell to the left of your Average formula (my cell "163").

Then, tap the Enter key.

Use the Fill-Down technique to propagate this newly formatted formula down to the last row of your data.

Of course, your weight values will be different from mine, so too will your Differences and your Averages.

CGreaves@ChrisGreaves.com

Search