Moving average

The resulting graph shows that while there is definitely a correleation between time spent exercising and increase in stamina the progress is a bit "bumpy". A better indicator of progress can be given by averaging the results over a longer period.

From the Chart menu select Add Trendline (or alternatively right-click on one of the data points and select the command from the pop-up menu). In the dialog box choose Number of steps in the Based on Series box, select  Moving average as Type and click the up arrow to give a Period of 3 and click OK.

The trendline for number of steps now parallels the one for exercise more closely and emphasises the relationship between the two.

The thick black trendline is rather unattractive to improve this right-click on the line, choose Format Trendline and in the Patterns tab select an alternative colour, say red, and a thinner weight.

The final Imporving Stamina chart is shown below. Cick inside the picture to view it.

Speed increase

Finally let's add a predictive trendline to a performance indicator that you think has room for improvement - for example time to run a mile. Let's make the assumtion that you can continue to increase your speed at the same rate for another 6 weeks. First extend the series in columns A and B to Week 18. To do this select A12:B13 (i.e. these cells in the final two rows in order to get the correct date series) and drag on the atofill handle down to B19. Now select the two ranges we want included in the chart, i.e. B1:B19 and G1:G19 (i.e. including six empty cells).

(Click inside picture to expand it)

To do this select the first column of cells with the mouse and the hold down the Ctrl key while selecting the second range and click the Chart Wizard icon. Create a line chart and give it the title Time to Run a Mile, label the y-axis and remove the legend by unticlkng the Show legend box in the Legends tab.

Again we want a line chart in a new chart window given the label Speed.

When the chart appears it only displays the data for which we have existing readings.

As before the trend seems rather "flat" due to the scale on the y-axis - so we'll reduce it as before. This time we need to replace the values in all four boxes in the Scale tab of the Format Axis dialog. Enter 500 against Minimum; 600 for Maximum; 20 for  Major Unit;  and 10 for Minor Unit.

Now add a trend line by right-clicking on one of the data points and choosing Add Trendline from the pop-up menu.

Select Linear in the Type tab of the Add Trendline box. Then click on the Options tab. We need to specify the number of perids to extend the trendline in the Forward box in the Forecast section. The value you might expect to use is 6 - but in fact Excel is working in days rather than weeks so the correct vaule is 42, i.e. 6 weeks expressed in days.

By default the trendline appears in black so to change this go to the Patterns tab and set the colour and weight you prefer - here red and a thinner line.

Finally let's use color to indicate the idea of striving towards a goal. Right-click on an empty area of the chart (or use the shortcut Ctrl+1) to open the Format Plot Area dialog.

Click on Fill Effects button and in the Gradient tab that opens ensure that One color is the selected option. Then choose a suitable color from the pop-up picker that appears when click on the down arrow to the right of the Color box. In the Shading styles list select Diagonal up and select the top left-hand variant. Notice that by sliding the shader between Dark and Light you alter the fill effect seen in the Sample box. Once you have the desired fill effect click on OK.

Here is the final chart - but of course yours could look very different as there as so many customization options.

(Click inside picture to expand it)

<ASIN:0470044004>