EXCEL 2013 BROKEN or rather the function LINEST() no longer works!!!

Andrew, also you did not explain why this function made no errors in Excel 2003, but has problems in later versions.  Somebody must have changed the algorithm without being aware of the cosequences.

George--you're also right that the example workaround mentioned in the KB Article I mentioned doesn't apply directly to this case--my bad. However, after doing some further investigation, I believe that the root cause of LINEST behaving badly in these cases is still the one mentioned in this article: Excel has a hard time handling input values that are extremely large. This particular case causes problems starting with Excel 2007.

In your example, you're using LINEST to get the coefficients of a third-order regression equation where the X values exceed 9188. This value (as part of an array) gets cubed before it is passed to LINEST. So one of the inputs to LINEST in this case is 9188^3 = ~775 billion.

Andrew, I do not concur with your thoughts and believes for reason that that LINEST drops the first order term 9189 which is a small number and it calculates correctly the regression for

y' = a' + c' x^2  + d' x^3 

using the large number 9189^3

Therefor, this problem is not related to the mention KB277585 article.

What I would like to know is if your engineers are fixing this problem and when this problem will be fixed otherwise I will ask for a healthy discount from my recent Office Professional 2013 purchase as Microsoft is supplying a product not fit for use. The consumer laws here in the UK are very well defined and I have a case where I can show that the product is not fit for use as defined in the specifications i.e. function references.

Andrew,

Thank you for taking this seriously.  There is no problem in Excel 2003, so why should there be a problem in 2007 and 2010 and 2013.  You guys just have to fix this.  It is interesting that the graphics displays this correctly, but the spreadsheet does not.  So you have an internal error here somewhere.

I also know of a researcher at Yale who had to revert back to excel 2003 because of problems with the later versions.  I will report on this next.

Best,

Georg

George and Anton,

Thanks for following up with this. George, your spreadsheet is very helpful in pinpointing the circumstances under which this error occurs.

George--you're also right that the example workaround mentioned in the KB Article I mentioned doesn't apply directly to this case--my bad. However, after doing some further investigation, I believe that the root cause of LINEST behaving badly in these cases is still the one mentioned in this article: Excel has a hard time handling input values that are extremely large. This particular case causes problems starting with Excel 2007.

In your example, you're using LINEST to get the coefficients of a third-order regression equation where the X values exceed 9188. This value (as part of an array) gets cubed before it is passed to LINEST. So one of the inputs to LINEST in this case is 9188^3 = ~775 billion.

When I extended your example to investigate fourth-order regressions, i noticed problems in Excel 2007 starting with X values of 956 or greater (raised to the fourth power, that's an input of 838 billion--around the same order of magnitude). You can download the spreadsheet here. I wasn't able to discover any errors in Excel 2007+ for second or first-order regressions.

Thanks again for your help in bringing this issue to our attention.

This is Andrew Stegmaier, and I'm a Program Manager on the Excel engineering team.

Unlike Mike H, I was able to reproduce the differences you noted beginning in 2007 (i.e. in 2003 it behaves one way, but in 2007, 2010 and 2013, it behaves another, way).

It appears that this might be very similar to the issue documented here:

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

The size of the x values is very large compared to the size of the y values. The solution, as outlined in the KB article, is to reduce X-Values by the same constant amount. When I subtract 9139 from each of the X-values in 2013 (and 2007), it causes me to get the same result as I got in 2003.

I understand that this doesn't fix the underlying problem, but it should provide a workaround.

Andrew, we don't need a workaround, which we had already figured out several months ago.  Just to let you know I reported this issue on 1/31/2013 to '******@mssupport.microsoft.com' but never got a reply.  I also reported it at the same time on the dedicated excel bug report on-line page - which was also ignored.  What sort of organization is Microsoft that it can afford to have such an arrogant attitude?

We have paid good money for Excel and need to have it working correctly.  Especially, since there are no other alternative spreadsheet applications available anymore.  I used Quattro in the past which at the time was much better than Excel, but Microsoft by virtue of having a monopoly has put everybody else out of business.  So Microsoft has a duty to make Excel error free, or else should refund everybody their money who have paid for Excel 2010 and 2013.

It is simply not good enough that the index(linest) function for a third order poynomial regression line stops working when x=9189, and for second order and fourth order polynomials at different x-values.

We use the linest fuction to determine exit and entry points for a financial model, starting in 1965 with daily data. So there are about 12,300 trading days in the period and the model stopped working at 9,189.  This is actually not funny, because investment decisions are made based on the model, and people could have lost a lot of money if we had not found that error in time.