XL: Regression Analysis and Best Fit Lines (XE0124)

• Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a
• Microsoft Excel 98 for the Macintosh

SUMMARY

The Application Note "Regression Analysis and Best Fit Lines" (XE0124), discusses how to use Microsoft Excel functions to perform simple, multiple, and polynomial regression analysis. It contains examples of how to use LINEST(), LOGEST(), TREND(), and GROWTH() to describe a best fit line or curve and to make predictions about your data. It also outlines some of the new statistical functions and tools available with Microsoft Excel versions 4.0 and later.

This Application Note is available for download from the Microsoft Software Library:

 ~ XE0124.exe (size: 61612 bytes) 

• Microsoft's World Wide Web Site on the Internet
• The Internet (Microsoft anonymous ftp server)
• Microsoft Download Service (MSDL)
• Microsoft Technical Support

   ARTICLE-ID: Q119591
   TITLE     : How to Obtain Microsoft Support Files from Online Services

   http://www.microsoft.com/worldwide/default.htm

TEXT OF XE0124

The following is the full text of the Application Note. (Note that some graphics and formatting may be missing from this version of the document. For best results, download the Microsoft Word version of this document.)

                                 OVERVIEW
                                 ========

   General Information
      To Use the Analysis ToolPak
      Choosing the Best Function
         When Your Data Is Linear
         When Your Data Is Exponential
         When Your Data Is Curvilinear

   Simple Regression Analysis
      Describing a Best-fit Line
         Finding the Slope and the Y Intercept
      Calculating a Best-fit Line
         Using TREND
         Using LINEST
      Plotting the Best-fit Line
         Plotting the Trendline Automatically
      Predicting Future Values
         Using FORECAST
         Using TREND
         Using LINEST

   Multiple Regression Analysis
      Predicting Y-Values
         Using TREND
         Using LINEST

   Polynomial Regression Analysis
      Calculating a Polynomial Curve
      Charting a Polynomial Curve

   Using Regression Statistics
         Using LINEST/LOGEST for Regression Statistics
         Using R2 to Test Regression Model Accuracy

GENERAL INFORMATION

Regression is a statistical method used to predict values based on relationships in existing data. By analyzing how a single dependent variable (y) is affected by the values of one or more independent variables (x), you can predict what y will be given x. You can use this information to fit a line or a curve to your existing data and to forecast future values. The LINEST, TREND, LOGEST, and GROWTH functions are the primary functions you will use to perform regression analysis in Microsoft Excel.

While this Application Note focuses primarily on the functions that can be used in Microsoft Excel versions 3.0 and later, Microsoft Excel versions 4.0 and later offer several new functions and tools that you can use to perform regression analysis and to create best-fit lines. When one of these new functions can be used to perform a task described in this Application Note, the function will be noted in the appropriate section. The following table lists some of these new functions.

                                        Use this
   To do this                           function
   ----------------------------------------------

   Return the correlation coefficient   CORREL
   for two arrays of cells

   Return a single predicted y-value    FORECAST
   based on a linear regression of
   known x and y ranges

   Return the y intercept of the linear INTERCEPT
   regression line

   Calculate R2, the coefficient of     RSQ
   Determination

   Return the slope of the linear       SLOPE
   regression line

   Return the standard error of the     STEYX
   Regression

   Table 1--Regression Analysis Functions in
   Microsoft Excel Versions 4.0 and later.

                                            Use this
   To do this                               analysis tool
   -------------------------------------------------------

   Predict a value based on the forecast    Exponential
   for the prior period, adjusted for the   Smoothing
   error in that prior forecast

   Project values in the forecast period    Moving Average
   based on the average value of the
   variable over a specific number of
   preceding periods

   Perform linear regression analysis and   Regression
   return statistics and plots as specified

               Table 2--Analysis ToolPak Add-in Features

To Use the Analysis ToolPak

In Microsoft Excel 5.0 and later:

1. On the Tools menu, click Data Analysis.

2. If the Data Analysis command is not available, click Add-Ins on the Tools menu. In the Add-Ins dialog box, click to select the Analysis ToolPak check box.

In Microsoft Excel 4.0:

1. On the Options menu, click Analysis Tools.

2. If the Analysis Tools command is not available, click Add-In on the Options menu. In the Add-Ins dialog box, click Add. Click Analysis.xla in the Library\Analysis folder.

3. In the Data Analysis dialog box, choose the tool that you want to use, such as Exponential Smoothing. For help on how to use a particular analysis tool, click Help in the dialog box for the tool.

CHOOSING THE BEST FUNCTION

Whether you are performing simple regression (one x variable), multiple regression (two or more x variables), or polynomial regression (one x variable raised to different powers), you will get the most accurate results if the function that you choose to regress your data is based on the patterns in your existing data.

When Your Data Is Linear

Your data is linear if the rate of change in your data is even to such an extent that when you plot it in a chart, the pattern in your data points resembles a line. If your data is linear, use the linear regression functions, LINEST and TREND. Both functions use the "least squares" method to calculate a straight line that best fits your data. LINEST returns information about the line, such as its slope and y intercept, and TREND returns predicted values along the line.

In Microsoft Excel versions 4.0 and later, the Regression tool (in the Analysis ToolPak add-in) performs linear regression, returns regression statistics, calculates best-fit lines, and creates best-fit line charts.

When Your Data Is Exponential

Your data is exponential if the rate of change in your data, when plotted on a chart, resembles a curve that rises or falls at an increasingly higher rate. If your data is exponential, use the logarithmic regression functions, LOGEST and GROWTH. LOGEST calculates an exponential curve that best-fits your data and, like LINEST, returns information about the curve. Like TREND, GROWTH returns predicted values along the curve.

When Your Data Is Curvilinear

To most accurately predict values when the pattern in your data is neither linear nor exponential, use polynomial regression in conjunction with the TREND function to calculate a best-fit curve. For example, use this method if, when you plot your data in a chart, it resembles a curve for which the rate of change is not dramatic or if your data fluctuates in such a way that no linear or curved pattern can be identified.

SIMPLE REGRESSION ANALYSIS

Your regression analysis is "simple" if you have only one independent x variable for each dependent y variable. For example, assume you are analyzing the sales figures for the first six months of operation for Wingtip Toys, a company that specializes in the design and manufacture of toys.

   NOTE: The following examples primarily use the LINEST and TREND
   functions. Wherever these two functions are discussed, LOGEST and
   GROWTH can be substituted if your data is exponentially curved and
   if a curve fit would be more accurate than a straight line.

The Regression tool in Microsoft Excel version 4.0 and later performs each of these tasks automatically.

For additional information on calculating regression, see the following references.

   Version of
   Microsoft Excel   Reference
   ------------------------------------------------------------

   97, 98            In Help, search for "Regression, Analysis"
   7.0               In Help, search for "Regression"
   5.0               In Help, search for "Regression "
   4.0               User's Guide 2, pages 41–45

Following are the sales figures for Wingtip Toys and the corresponding months in both table and chart form.

         A       B
   1   Month   Sales
   2        1  $4,200
   3        2  $6,100
   4        3  $7,300
   5        4  $7,300
   6        5  $8,700
   7        6  $10,500

         Table 3--Sample Data (Sales Figures for Wingtip Toys)

DESCRIBING A BEST-FIT LINE

The equation of a straight line is y=mx+b, where m is the slope and b is the y intercept. LINEST returns the slope (m) and y intercept (b) values that describe the line derived from your existing data.

Microsoft Excel versions 4.0 and later provide specific SLOPE and INTERCEPT functions for calculating the slope and the y intercept when your data is linear.

For additional information, see the following references.

   Version of
   Microsoft Excel   Reference
   ------------------------------------------------------------

   97, 98            In Help, search for "slope" or "intercept"
   7.0               In Help, search for "slope" or "intercept"
   5.0               In Help, search for "slope" or "intercept"
   4.0               Function Reference Guide, pages 405–406

   NOTE: If your data is exponentially curved, use LOGEST to return
   the slope (m) and y intercept (b) values that describe the curve.
   The equation used by LOGEST is y=b*m^x.

Finding the Slope and the Y Intercept

To calculate the values of the slope (m) and y intercept (b), use the procedure appropriate for your version of Microsoft Excel.

Microsoft Excel 4.0 and later:

1. Use the data in Table 3--Sample Data (Sales Figures for Wingtip Toys).

2. To find the slope, select cell E2 and type the following formula:

         =SLOPE(B2:B7,A2:A7)
   

1. To find the y intercept, select cell F2, and type the following formula:

         =INTERCEPT(B2:B7,A2:A7)
   

Microsoft Excel 3.0:

1. Using the data in "Table 3--Sample Data (Sales Figures for Wingtip Toys)," select cells E2:F2.

2. Type the following formula:

         =LINEST(B2:B7,A2:A7)
   

   NOTE: Because the function returns data to more than one cell, you must enter the formula as an array by pressing CTRL+SHIFT+ENTER in Microsoft Excel for Windows or COMMAND+RETURN in Microsoft Excel for the Macintosh.

   NOTE: LINEST also takes other optional arguments that are not
   necessary for this example.

        E        F
   1  Slope      Y intercept
   2  1122.857    3420

   Table 4--Example of Slope Intercept Values

CALCULATING A BEST-FIT LINE

If your data is linear, use TREND or LINEST to calculate your best-fit line. In Microsoft Excel versions 4.0 and later, you can also use the FORECAST function (forecast is mainly useful for finding a data point based on existing data, but can it also be used as a substitute for the TREND function). If your data fits an exponential curve, use LOGEST or GROWTH.

Using TREND

The TREND function is the easiest and most efficient function for calculating the points along a best-fit line. To simultaneously calculate all the values on the best-fit line, do the following:

1. Using the data in Table 3 of this Application Note, select cells C2:C7 and type the following formula:

         =TREND(B2:B7,A2:A7)
   

   NOTE: Because the function returns data to more than one cell, you must enter the formula as an array by pressing CTRL+SHIFT+ENTER in Microsoft Excel for Windows or COMMAND+RETURN in Microsoft Excel for the Macintosh.

   NOTE: TREND also takes other optional arguments that aren't
   necessary for this example.

Using LINEST

You can also use the slope (m) and the y intercept (b) values returned by LINEST to find data points on the best-fit line by substituting the y- values or the x-values into the equation for a line (y=mx+b). By plugging each month number into this formula, you can calculate all the data points for your best-fit line. With the slope(m) value (1122.857) in cell E2 and the intercept (b) value (3420) in cell F2, do the following to generate the points on your best-fit line:

1. Select cell D2 and enter the following formula (because the formula only references single cells, it is not necessary to enter this formula as an array):

         =($E$2*A2)+$F$2
   

2. Select cells D2:D7.

3. Use the appropriate method for you version of Microsoft Excel.

       - In Microsoft Excel 5.0 and later, point to Fill on the Edit menu,
         and click Down.
   

       - In Microsoft Excel 4.0 and earlier, click Fill Down on the Edit
         menu.
   

The following table shows the results of the values returned when you use TREND and LINEST.

        A        B          C             D           E           F
   -----------------------------------------------------------------------
   1  Month    Sales   Predicted Y   Predicted Y    Slope    Y Intercept
                          TREND         LINEST
   -----------------------------------------------------------------------
   2       1    $4,200       $4,543        $4,543  1122.857          3420
   3       2    $6,100       $5,666        $5,666
   4       3    $7,300       $6,789        $6,789
   5       4    $7,300       $7,911        $7,911
   6       5    $8,700       $9,031        $9,031
   7       6   $10,500      $10,157       $10,157

  Table 5--Results of the Values Returned When You Use TREND and LINEST.

   NOTE: The returned values for TREND and LINEST are identical.

PLOTTING THE BEST-FIT LINE

Once you have calculated the values on your best-fit line, you can add that line to your existing chart by copying cells C1:C7 and pasting them into your existing chart.

The resulting chart will have a straight line (best-fit line) running through your original data.

   NOTE: When you display the points of a best-fit line against the
   original data in a chart, in most cases you will get the best results
   by using an xy (scatter) chart. If you use a line chart, the x-values
   will be treated as labels rather than as values, and curved lines may
   result.

Plotting the Trendline Automatically

In Microsoft Excel versions 5.0 and later, you can insert a trendline directly into your chart without having to first calculate the points or copy and past those points into the chart. Do the following to automatically insert a trendline in a chart:

1. Double-click the chart to activate it.

2. Select the series for which you want to plot a trendline.

3. Use the procedure appropriate for your version of Microsoft Excel:

   If you are using Microsoft Excel 97 or 98, click Add Trendline on the Chart menu. In the Trendline dialog box, click the Type tab, and then select the type of trend or regression you want to plot. Click OK.

   If you are using Microsoft Excel 5.0 or 7.0, click Trendline on the Insert menu.

PREDICTING FUTURE VALUES

In addition to returning values along the line fitted to your existing data, you can use TREND and LINEST to predict future values. In Microsoft Excel 4.0 and later, you can also use the FORECAST function to predict future values.

Using the Wingtip Toys example, suppose you want to calculate sales figures for months 7, 8, and 9. The following examples show how to accomplish this using the FORECAST, TREND, and LINEST functions, respectively.

To predict values for months 7, 8, and 9, first enter the month numbers for which you want predicted sales figures and then use the FORECAST function to calculate the values.

Using FORECAST

1. In cells A8:A10, type 7, 8, and 9, respectively.

2. Select cells B8:B10.

3. Type the following formula:

         =FORECAST(A8:A10,B2:B7,A2:A7)
   

   NOTE: Because the function returns data to more than one cell, you must enter the formula as an array by pressing CTRL+SHIFT+ENTER in Microsoft Excel for Windows or COMMAND+RETURN in Microsoft Excel for the Macintosh.

The resulting values in cells B8:B10 are the predicted sales for the next three months.

         A       B
   1   Month   Sales
   2     1     $4,200
   3     2     $6,100
   4     3     $7,300
   5     4     $7,300
   6     5     $8,700
   7     6     $10,500
   8     7     $11,280
   9     8     $12,403
   10    9     $13,526

   Table 6--Sample Data Using TREND to Predict Future Values Using TREND