LINEST Function Returns Incorrect Values

[JoeMo]

I have a simple set of X-Y data as shown below. When I chart the data using a scatter plot and Excel 2007, it's apparent by eye that the points fall on a good straight line. If I use: Chart Tools/Layout/Trendline and select a linear fit, a line is fit to the data points with an R^2 value of 0.9955 and an equation:
y = -4E+07X - 1.9692. This all makes sense and confirms what the eye sees on the chart.

If I use the LINEST function on the same data set, it returns the nonsensical values of slope and Y-intercept shown below. Note: I have not forced a zero intercept, the X- and Y-ranges are not overlapping and the number of data rows exceeds the number of data columns, so none of the known reasons for LINEST giving incorrect results is present.

Any explanations?

Excel Workbook
	A	B	C	D	E
1				from LINEST
2	X	Y		Slope	Y-Intercept
3	7.3E-07	-28.9536		0	-32.5707215
4	7.6E-07	-30.5338
5	8.2E-07	-32.2276
6	8.7E-07	-34.3473
7	9.36E-07	-36.7913
Sheet1

 

[Aladin Akyurek]

You seem to have small values... See whether this helps:

http://support.microsoft.com/kb/976468

 

[pgc01]

Hi Joe

I used the Linest() (your formula) on the values you posted and got the results below.

I then calculated the estimates for Y given the Slope and Intercept from Linest() and got acceptable values.

Can you check?


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >E</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >F</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border-bottom-width: 2px;border-color:#000000;border-top-width: 2px;border-color:#000000;border-left-width: 2px;border-color:#000000;border-Right-width: 2px;border-color:#000000; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border-bottom-width: 2px;border-color:#000000;border-top-width: 2px;border-color:#000000;border-left-width: 2px;border-color:#000000;border-Right-width: 2px;border-color:#000000; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border-bottom-width: 2px;border-color:#000000;border-top-width: 2px;border-color:#000000;border-left-width: 2px;border-color:#000000;border-Right-width: 2px;border-color:#000000; "> </td><td colspan=2 rowspan=1 style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:center;border-bottom-width: 2px;border-color:#000000;border-top-width: 2px;border-color:#000000;border-left-width: 2px;border-color:#000000; ">from LINEST</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-decoration:underline;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">X</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-decoration:underline;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">Y</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:left;border: solid 2px #000000; ">Y Estimate</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-decoration:underline;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">Slope</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-decoration:underline;background:#FFFFFF;text-align:right;border-bottom-width: 2px;border-color:#000000;border-top-width: 2px;border-color:#000000;border-left-width: 2px;border-color:#000000;border-Right-width: 2px;border-color:#000000; ">Y-Intercept</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">7.30E-07</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">-28.9536</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">-29.106</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">-37,173,690.083</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">-1.969</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">7.60E-07</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">-30.5338</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">-30.221</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border-bottom-width: 2px;border-color:#000000;border-top-width: 2px;border-color:#000000;border-left-width: 2px;border-color:#000000;border-Right-width: 2px;border-color:#000000; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">8.20E-07</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">-32.2276</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">-32.452</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border-bottom-width: 2px;border-color:#000000;border-top-width: 2px;border-color:#000000;border-left-width: 2px;border-color:#000000;border-Right-width: 2px;border-color:#000000; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">8.70E-07</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">-34.3473</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">-34.310</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border-bottom-width: 2px;border-color:#000000;border-top-width: 2px;border-color:#000000;border-left-width: 2px;border-color:#000000;border-Right-width: 2px;border-color:#000000; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border-bottom-width: 2px;border-color:#000000;border-top-width: 2px;border-color:#000000;border-left-width: 2px;border-color:#000000;border-Right-width: 2px;border-color:#000000; ">9.36E-07</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border-bottom-width: 2px;border-color:#000000;border-top-width: 2px;border-color:#000000;border-left-width: 2px;border-color:#000000;border-Right-width: 2px;border-color:#000000; ">-36.7913</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border: solid 2px #000000; ">-36.764</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border-bottom-width: 2px;border-color:#000000;border-top-width: 2px;border-color:#000000;border-left-width: 2px;border-color:#000000;border-Right-width: 2px;border-color:#000000; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFFFF;text-align:right;border-bottom-width: 2px;border-color:#000000;border-top-width: 2px;border-color:#000000;border-left-width: 2px;border-color:#000000;border-Right-width: 2px;border-color:#000000; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>8</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=7 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>

 

[shg]

I get what PGC gets using Excel 2007, and what you get using Excel 2003.

You can change the formula in Excel 2003 to get the same results as in 2007 by normalizing the x values:

=LINEST(B3:B7, A3:A7 / MAX(ABS(A3:A7))) / MAX(ABS(A3:A7)) ^ {1,0}

 

[JoeMo]

Aladin, PGC and Shg,

Many thanks for your replies.

Aladin, the link you provided indicates that LINEST in Excel 2007 can return incorrect values if the function encounters column magnitudes that are very small (less than 4.5474735088646412e-013).This is several orders of magnitude smaller than my values. The fix requires a download and a registry change which I will try when time permits.

PGC, you get the correct values. What version of Excel are you using?

Shg, your normalized x values formula gives me the correct results (still in Excel 2007).

Clearly, my version of Excel 2007 which has been getting regular Windows updates since its installation is missing something that Shg's (and maybe PGC if using 2007) has. Possibly the fix Aladin referred me to will rectify my version of 2007. What I find really puzzling is that the chart tools in my version can produce the correct slope and intercept when the LINEST function itself does not. Note that the chart trend line gave a slope of -4E7 which is the -37,173,690 value rounded. The intercept from the chart gives the expected -1.969 value.

 

[shg]

Trendline regression in charts and LINEST is different, and if the chart methods are described somewhere, I'm yet to find it.

I've had the registry fix installed for a while; that may be why it works for me in 2007.

 

[JoeMo]

Trendline regression in charts and LINEST is different, and if the chart methods are described somewhere, I'm yet to find it.

I've had the registry fix installed for a while; that may be why it works for me in 2007.

Shg,
From your post, I gather that the registry fix is something that doesn't come via Windows and Office 2007 automatic updates. I'll go ahead and apply it using the link Aladin posted. Thanks.

I can't find a description of how the chart tools do curve fitting either, but I did find a reference indicating that the TREND function uses LINEST on its way to forecasting future trends. I would have thought the quickest way to fit a straight line to data points on a chart would be to call the LINEST function, but evidently that's not the case.