# Project values in a series

Whether you need to forecast expenses for the next year or project the expected results for a series in a scientific experiment, you can use Microsoft Office Excel to automatically generate future values that are based on existing data or to automatically generate extrapolated values that are based on linear trend or growth trend calculations.

You can fill in a series of values that fit a simple linear trend or an exponential growth trend by using the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) or the **Series** command. To extend complex and nonlinear data, you can use worksheet functions or the regression analysis (regression analysis: A form of statistical analysis used for forecasting. Regression analysis estimates the relationship between variables so that a given variable can be predicted from one or more other variables.) tool in the Analysis ToolPak Add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.).

- Fill in a series for a linear best-fit trend automatically
- Fill in a series for a growth trend automatically
- Fill in linear trend or growth trend values manually
- Calculate trends by adding a trendline to a chart
- Project values with a worksheet function
- Perform regression analysis with the Analysis ToolPak Add-in

## Fill in a series for a linear best-fit trend automatically

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

Initial selection |
Extended linear series |
---|---|

1, 2 | 3, 4, 5 |

1, 3 | 5, 7, 9 |

100, 95 | 90, 85 |

To fill in a series for a linear best-fit trend, do the following:

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

If you want to increase the accuracy of the trend series, select additional starting values.

- Drag the fill handle in the direction that you want to fill with increasing values or decreasing values.

For example, if the selected starting values in cells C1:E1 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 or to use the keyboard to fill in a series, click the **Series** command (**Home** tab, **Editing** group, **Fill** button).

## Fill in a series for a growth trend automatically

In a growth 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 are then multiplied by the step value.

Initial selection |
Extended growth series |
---|---|

1, 2 | 4, 8, 16 |

1, 3 | 9, 27, 81 |

2, 3 | 4.5, 6.75, 10.125 |

To fill in a series for a growth trend, do the following:

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

If you want to increase the accuracy of the trend series, select additional starting values.

- Hold down the right mouse button, drag the fill handle in the direction that you want to fill with increasing values or decreasing values, release the mouse button, and then click
**Growth Trend**on the shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.).

For example, if the selected starting values in cells C1:E1 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 or to use the keyboard to fill in a series, click the **Series** command (**Home** tab, **Editing** group, **Fill** button).

## Fill in linear trend or growth trend values manually

When you click the **Series** command, you can manually control how a linear trend or growth trend is created and then use the keyboard to fill in the values.

- In a linear series, the starting values are applied to the least-squares algorithm (y=mx+b) to generate the series.
- In a growth series, the starting values are applied to the exponential curve algorithm (y=b*m^x) to generate the series.

In either case, the step value is ignored. The series that is created is equivalent to the values that are returned by the TREND function or GROWTH function.

To fill in the values manually, do the following:

- Select the cell where you want to start the series. The cell must contain the first value in the series.

When you click the **Series** command, the resulting series replaces the original selected values. If you want to save the original values, copy them to a different row or column, and then create the series by selecting the copied values.

- On the
**Home**tab, in the**Editing**group, click**Fill**, and then click**Series**.

- Do one of the following:
- To fill the series down the worksheet, click
**Columns**. - To fill the series across the worksheet, click
**Rows**. - In the
**Step value**box, enter the value that you want to increase the series by.

Series type |
Step value result |
---|---|

Linear | The step value is added to the first starting value and then added to each subsequent value. |

Growth | The first starting value is multiplied by the step value. The resulting product and each subsequent product are then multiplied by the step value. |

- Under
**Type**, click**Linear**or**Growth**. - In the
**Stop value**box, enter the value that you want to stop the series at.

** Note ** If there is more than one starting value in the series and you want Excel to generate the trend, select the **Trend** check box.

## Calculate trends by adding a trendline to a chart

When you have existing data for which you want to forecast a trend, you can create a trendline (trendline: A graphic representation of trends in data series, such as a line sloping upward to represent increased sales over a period of months. Trendlines are used for the study of problems of prediction, also called regression analysis.) in a chart. For example, if you have a chart in Excel that shows sales data for the first several months of the year, you can add a trendline to the chart that shows the general trend of sales (increasing or decreasing or flat) or that shows the projected trend for months ahead.

This procedure assumes that you already created a chart that is based on existing data. If you have not done so, see the topic Create a chart.

- Click the chart.
- Click the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) to which you want to add a trendline (trendline: A graphic representation of trends in data series, such as a line sloping upward to represent increased sales over a period of months. Trendlines are used for the study of problems of prediction, also called regression analysis.) or moving average (moving average: A sequence of averages computed from parts of a data series. In a chart, a moving average smooths the fluctuations in data, thus showing the pattern or trend more clearly.).
- On the
**Layout**tab, in the**Analysis**group, click**Trendline**, and then click the type of regression trendline or moving average that you want. - To set options and format the regression trendline or moving average, right-click the trendline, and then click
**Format Trendline**on the shortcut menu. - Select the trendline options, lines, and effects that you want.
- If you select
**Polynomial**, enter in the**Order**box the highest power for the independent variable. - If you select
**Moving Average**, enter in the**Period**box the number of periods to be used to calculate the moving average.

** Notes **

- The
**Based on series**box lists all the data series in the chart that support trendlines. To add a trendline to another series, click the name in the box, and then select the options that you want. - 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 may need to sort the x values before you add a moving average.

## Project values with a worksheet function

**Using the FORECAST function** The FORECAST function calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, and consumer trends.

**Using the TREND function or GROWTH function** The TREND and GROWTH functions can extrapolate future *y*-values that extend a straight line or exponential curve that best describes the existing data. They can also return only the *y*-values based on known *x*-values for the best-fit line or curve. To plot a line or curve that describes existing data, use the existing *x*-values and *y*-values returned by the TREND or GROWTH function.

**Using the LINEST function or LOGEST function** You can use the LINEST or LOGEST function to calculate a straight line or exponential curve from existing data. The LINEST function and LOGEST function return various regression statistics, including the slope and intercept of the best-fit line.

The following table contains links to more information about these worksheet functions.

Function |
Description |
---|---|

FORECAST | Project values |

TREND | Project values that fit a straight trend line |

GROWTH | Project values that fit an exponential curve |

LINEST | Calculate a straight line from existing data |

LOGEST | Calculate an exponential curve from existing data |

## Perform regression analysis with the Analysis ToolPak Add-in

When you need to perform more complicated regression analysis — including calculating and plotting residuals — you can use the regression analysis tool in the Analysis ToolPak Add-in.

- On the
**Add-ins**menu, in the**Menu Commands**group, click**Data Analysis**.

If the the **Add-ins** tab or the **Data Analysis** command is not available, load the Analysis ToolPak.

How to load the Analysis ToolPak

- Click the
**Microsoft Office Button**, click**Excel Options**, and then click the**Add-ins**category.

- In the
**Manage**list, select**Excel Add-ins**, and then click**Go**. - In the
**Add-ins available**list, select the**Analysis ToolPak**box, and then click**OK**. - If necessary, follow the instructions in the Setup program.

- In the
**Data Analysis**dialog box, select the name of the analysis tool that you want, then click**OK**. - In the dialog box for the tool that you selected, select the analysis options that you want.

You can click the **Help** button in the dialog box to get more information about the options.