About projecting values - Excel - Microsoft Office Online

Click here to switch to other Office Online sites worldwide

United States (change) | All Microsoft Sites

	Microsoft Office Excel

Search

	Check for free updates
Office downloads

Microsoft Office Excel

» Excel Home

Product Information

» Overview

» Top 10 Reasons to Try

» See It in Action

» Pricing Information

» Free 60-day Trial

» Buy Now

Help and How-to

» Excel 2007 Help

» Excel 2003 Help

» Excel 2002 Help

» Excel 2000 Help

Choose your version

» Excel Web Access

Training

» Excel 2007 Training

» Excel 2003 Training

» Excel 2007 Demos

» Excel 2003 Demos

» Excel 2007 Webcasts

» Excel 2003 Webcasts

Templates

» Excel Templates

» Submit a Template

Filling in values automatically

Show Filling in values for a linear best-fit trend

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

Show Filling in values for an exponential growth trend

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 is 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

Filling in values manually

When you use the Series command, you can manually control how a linear or growth trend is created and use the keyboard to fill in 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 created is equivalent to the values returned by the TREND function or GROWTH function.

Calculating trends by adding a trendline to a chart

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 without creating the data for the trendline.

Projecting values with a worksheet function

Show Using the FORECAST function

The FORECAST function predicts new values based on a least-squares linear regression of a range of known data or known x-arrays and y-arrays. For example, given corporate earnings for each of the previous six quarters, the FORECAST function returns earnings expected for the next two quarters.

Show Using the TREND or GROWTH functions

The TREND function and GROWTH function 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.

Show Using the LINEST 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.

Performing regression analysis with the Analysis ToolPak add-in

When you need to perform more complicated 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.)— including calculating and plotting residuals— you can use the regression analysis tool in the Analysis ToolPak add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.). The Regression analysis tool performs linear regression analysis by using the "least squares" method to fit a line through a set of observations. With it, you can analyze how a single dependent variable is affected by the values of one or more independent variables.