SLOPE/INTERCEPT problems with missing data

[Guest]

For those using Excel 2003, there is a bug in the SLOPE/INTERCEPT
formulas. If a y data point is deleted at the first or last position of
the data set, these functions will compute incorrectly and try to match
the last or first value, respectively, of y in the line intercept. If a
middle value is deleted, the formulas compute apparently correctly.
By deleted I mean that the ranges for x and y are consistent, except cells are left empty (data is missing or ignored).

One would ask why not just chage the range. The thing is this worked without problems in Excel 2000, XP. It's a big hassle to go through all files based on a particular template. Once opened in Excel 2003, these files compute SLOPE/INTERCEPT incorrectly.

 

[Jerry W. Lewis]

Please provide a simple example (text not an attachment)

Jerry

 

[Trancemission]

Hi Jerry,

x y
2 SLOPE 0.0050
4 0.0214 INTERCEPT 0.0063
8 0.0232 RSQ 0.947
12 0.0643
16 0.0778
20 0.1025

However, from the plotted data we get the correct
information

SLOPE 0.0054
INTERCEPT -0.0072
RSQ 0.9472

Also
x y
2 0.0066 SLOPE 0.0053
4 INTERCEPT -0.0001
8 0.0232 RSQ 0.965
12 0.0643
16 0.0778
20 0.1025

Again, from the plotted data we get the correct information

SLOPE 0.0055
INTERCEPT -0.0094
RSQ 0.9651

Thanks

-----Original Message-----
Please provide a simple example (text not an attachment)

Jerry

consistent, except cells are left empty (data is missing
or ignored).is this worked without problems in Excel 2000, XP. It's a
big hassle to go through all files based on a particular
template. Once opened in Excel 2003, these files compute
SLOPE/INTERCEPT incorrectly.

 

[Jerry W. Lewis]

I agree with you on what the results should be and that that the actual
results are wrong. However, I understand that you have a hypothesis
about what Excel 2003 is doing to get the wrong answer, and I am not
clear on what that hypothesis is.

It appears that the improved algorithm for RSQ was implemented properly.

Jerry