GNOME Bugzilla – Bug 597069
LINEST to skip invalid data
Last modified: 2009-10-02 18:26:22 UTC
I'm doing polynomial regressions with the following data set (I've omitted the x1-x8 squared numbers)... x1 = 3.5, y1 = <...actually, an "" from an IF(A1="","",calculation)> x2 = 7.7, y2 = x3 = 8.0, y3 = x4 = 11, y4 = 0.0340 x5 = 16, y5 = 0.0376 x6 = 22, y6 = 0.0424 x7 = 32, y7 = x8 = 64, y8 = 0.0909 LINEST does not process this, produces errors because of the empty cells. If I delete the IF equations from the cells, LINEST processes them as 0's. An empty cell is not a 0. LINEST and other functions ought to have a way to skip invalid data, like the regressions in the chart wizard.
First of all it does _not_ produce errors because of empty cells, but because of the specific empty strings. As long as we want this function to be XL compatible we unfortunately need to interpret empty cells as 0.
*** This bug has been marked as a duplicate of bug 551234 ***
Why not make a "LINEST2" function, so that there can still be compatibility with Excel, which does things the wrong way; and so that those of us who want to do things the right way can?
Note that I just tried LINEST on Excel 2007, and things seems to have changed. Excel now returns an error in the presence of empty cells. While this is still not great it would at least remove the silent change of empty to 0.