Predict data trends

You can extend a series of values that fit either a simple linear trend or an exponential growth trend by using the fill handle (fill handle: The lower-right corner of the selection. When you hold the pointer over the fill handle, the pointer changes to indicate that it is selected. You can copy contents to adjacent cells or fill in a series, such as dates, by dragging the fill handle.).

Use the following procedures to learn how to display and predict trends and create a projection.

ShowCreate a trend series from your data

Create a linear best-fit trend series

In a linear trend series, the step value — the difference between the starting value and the next value in the series — is added to the starting value and then added to each subsequent value.

  1. Select at least two cells that contain the starting values for the trend.

To increase the accuracy of the trend series, select more than two starting values.

  1. Drag the fill handle in the direction of either increasing or decreasing values.

For example, if you selected cells C1:E1 and the starting values in these cells are 3, 5, and 8, drag the fill handle to the right to fill with increasing trend values, or drag it to the left to fill with decreasing values.

 Tip   To manually control how the series is created, on the Edit menu, point to Fill, and then click Series.

Create an exponential growth trend series

In a growth trend series, the starting value is multiplied by the step value to get the next value in the series. The resulting product and each subsequent product is then multiplied by the step value.

  1. Select at least two cells that contain the starting values for the trend.

To increase the accuracy of the trend series, select more than two starting values.

  1. Hold down CONTROL and drag the fill handle in the direction that you want to fill with increasing or decreasing values.

For example, if you selected cells C1:E1 and the starting values in these cells are 3, 5, and 8, drag the fill handle to the right to fill with increasing trend values, or drag it to the left to fill with decreasing values.

  1. Release CONTROL , and the mouse button, and then on the contextual menu, click Growth Trend.

Excel automatically calculates the growth trend and continues the series in the selected cells.

 Tip   To manually control how the series is created, on the Edit menu, point to Fill, and then click Series.

ShowDisplay the trend series by using a trendline in a chart

You can use trendline (trendline: A graphical representation of the trend, or direction, of data in a series. Trendlines are used for the study of problems of prediction, also called regression analysis. You can add trendlines to data series in unstacked 2-D area, bar, column, line, stock, xy (scatter), and bubble charts.) and moving average (moving average: A sequence of averages computed from the parts of a data series. In a chart, a moving average smooths the fluctuations in data to show the pattern or trend more clearly.) in two-dimensional charts to graphically display trends and analyze problems of prediction (also known as regression analysis). A moving average smooths out fluctuations in data and shows the pattern or trend more clearly. By using regression analysis, which estimates the relationship between variables, you can extend a trendline in a chart beyond the actual data to predict future values.

  1. On your chart, click the data series to which you want to add a trendline or moving average.
  2. On the Chart menu, click Add Trendline, and then click Type.

 Note    You can add a trendline only to unstacked, 2-D, area, bar, column, line, stock, xy (scatter), or bubble charts.

  1. Click the kind of trendline or moving average that you want to use.
  2. Do one of the following:
If you select Do this
Polynomial In the Order box, enter the number for the highest power that you want to use for the independent variable.
Moving Average In the Period box, enter the number of periods that you want to use for calculating the moving average.
  1. Click OK.

 Note   If you add a moving average to an xy (scatter) chart, the moving average is based on the order of the x values plotted in the chart. To get the result that you want, you might have to sort the x values before you add a moving average.

See also

FORECAST function

TREND function

GROWTH function

LINEST function

LOGEST function

 
 
Applies to:
Excel for Mac 2011