Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Incorrect output is returned when you use the Linear Regression (LINEST) function in Excel

Incorrect output is returned when you use the Linear Regression (LINEST) function in Excel

Symptoms

When you use the LINEST worksheet function in a worksheet in Microsoft Excel, the statistical output may return incorrect values. The Regression tool in the Analysis ToolPak may also return incorrect values.

↑ Back to the top

Cause

The output returned from LINEST may be incorrect if one or more of the following conditions are true:

  • The range of x-values overlaps the range of y-values.
  • The number of rows in the input range is less than the number of columns in the total range (x-value plus y-value).
  • You specify a zero constant (set the third argument of the LINEST function to True).

↑ Back to the top

Workaround

Case 1: The x-value and y-value ranges overlap

If the x-value and y-value ranges overlap, the LINEST worksheet function produces incorrect values in all result cells. Normal statistical probability disallows the values in the x and y ranges to overlap (duplicate each other). Do not overlap the x- and y-value ranges when referencing cells in the formula.

Note The Regression tool alerts you to this problem and does not continue. You can use the Regression tool instead of the LINEST worksheet function.

In Microsoft Office Excel 2007, you can find the Regression tool by clicking Data Analysis in the Analysis group on the Data tab.

In Microsoft Office Excel 2003 and in earlier versions of Excel, you can find the Regression tool by clicking Data Analysis on the Tools menu.

Case 2: The number of rows is less than the number of x-columns

It is not statistically valid for the number of rows to be less than the number of x (variable) columns. The number of rows of data must be larger than the number of columns of data (x-columns plus y-columns).

Case 3: You specify a zero constant

Do not specify a zero constant (b=0) in the function.

↑ Back to the top

More Information

The Regression tool is included in the Analysis ToolPak. The Analysis ToolPak is an Excel add-in program. It is available when you install Microsoft Office or Excel. Before you use the Regression tool in Excel, you have to load the Analysis ToolPak.

To do this in Excel 2007, follow these steps:
  1. Click the Microsoft Office Button, and then click Excel Options.
  2. Click Add-Ins, and then select Excel Add-ins in the Manage box.
  3. Click Go.
  4. In the Add-Ins available box, click to select the Analysis ToolPak check box, and then click OK.

    Note If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it.
To do this in Excel 2003 and in earlier versions of Excel, follow these steps:
  1. On the Tools menu, click Add-Ins.
  2. In the Add-Ins box, click to select the Analysis ToolPak, and then click OK,

    Note If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it.

↑ Back to the top

References

Statistical Computations on a Digital Computer. William J. Hemmerle. Blaisdell Publishing Company: 1967. Chapter 3, "Multiple-Regression Computations" and section 3.2.1, "Preliminary Regression Theory."

↑ Back to the top

Keywords: kb, kbbillprodsweep, kbpending, kbbug, kbexpertisebeginner, kbtshoot, kbprb, kbfreshness2006, kboffice12yes

↑ Back to the top

Article Info
Article ID : 214230
Revision : 8
Created on : 8/20/2020
Published on : 8/21/2020
Exists online : False
Views : 155