1. Enter "Mowing Time (hrs)" in cell A1.
2. Enter "Water Consumption (oz)" in cell B1.
3. Enter values shown below in exact order in the appropriate columns of Excel’s spreadsheet.

1. Select TOOLS > DATA ANALYSIS.
2. In the menu box, select MOVING AVERAGE.
3. Click OK.
4. Enter the data set (B2:B8) in the Input Range box.
5. Enter "3" in Interval box. (In this example, we use 3 time periods)
6. Enter C1 in Output range.
7. Click OK. The smoothed values will be recorded in column C of the spreadsheet.

   8.  Register C1 as Smoothed Values (oz), and delete #N/A in C2.

Remark: Typically, moving average technique is eligible for both forecasting and smoothing. Thus, we have to be very careful when making use of any preprogrammed procedure of moving average. Moving average analysis tool in MS Excel is specifically designed for forecasting not creating the smoothed curve, for example, in our case, 25.66666667 is a projected value of period four instead of a smoothed value of period two, 31 is a forecasted value of period five instead of a smoothed value of period three, and so on. Thus, every time this analysis tool in MS Excel is utilized we have to modify the original results to fit to our needs.

1. Select INSERT > CHART (The shortcut is the chart icon.)

1. In the left menu box, select Line.
2. In the right menu box, select Line with markers.
3. Click Next

1. On the Data Range Tab

1. In the Data Range box the cell locations of the data and data labels (B1:C8) should appear. If not, then enter B1:C8
2. The radio button beside "data series is in" Columns should be marked.
3. Click Next.

1. On the Series Tab

1. Rename series 1 as Water Consumption in Name box.
2. Enter X values (A2:A8) in Category (X) axis labels box.
3. Rename series 2 as Smoothed Value in Name box.
4. Make sure that A2:A8 appear in Category (X) axis labels box.

1. Click Next.

1. On the Titles Tab

1. Enter the title of the graph: The Smoothed Curve of Water Consumption Based on Mowing Period.
2. Enter Mowing Time (hrs) in the value of the x-axis.
3. Enter Water Consumption (oz) in the value of the y-axis.

1. Feel free to check on the other tabs. You may wish to change some values.
2. Click Next.
3. Mark the radio button of As new sheet. Name the chart smoothed curve.
4. Click Finish to see the graph.
5. Save the worksheet. Select FILE > SAVE AS. Call the file esmoothmowtemp.

The smoothed curve generated by MS Excel is... 

Return to Example             Return to Concept

Regression Tutorial Menu    Dictionary

STATS @ MTSU