Bug in SLOPE/INTERCEPT forumulas in Excel 2003

[Trancemission]

I'm posting this again...

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
don't compute correctly, either.

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 change 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.

EXAMPLE:

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 (agrees with Excel 2000 and XP)

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

 

[Harlan Grove]

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
don't compute correctly, either.

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 change 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.

EXAMPLE:

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 (agrees with Excel 2000 and XP)

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

Strictly speaking this may not be a bug. It's a departure from functionality in
previous versions, but online help in Excel 97 at least is silent on how blank
cells in either x or y arrays would be treated. Granted changing functionality
between versions should at the very least be made explicit in the documentation
(now just online help), but Microsoft could claim that prior functionality with
respect to blank cells was unintended and from 2003 (11) on deprecated.

Microsoft did change the algorithms used in the regression functions in 2003
(11). Looks like they missed this aspect. Maybe this'll be fixed (returned to
prior functionality), maybe it won't. Personally, I'd put the bug in RAND() in
2003 (11) as a higher priority as it's an unambiguous bug (functionality
definitely inconsistent with specifications).

 

[Jerry W. Lewis]

While I grant developers the right to change functionality, If this were
an intentional change, I would have expected a similar change in RSQ()
and CORREL(). Given that there isn't, I assume that it was an oversight
in the algorithm conversion (and probably the result of inadequate
specifications/source code documentation).

Jerry

 

[Jonathan B. Marder]

The same bug affects the FORECAST function (I reported this on 11 Dec in in
message Excel 2003 is treating
intervening blank cells in the Y-array as if they were zero values. I don't
think this can be excused as "changed functionality" - it is definitely an
error, that greatly detracts from the utility of the function.

 

[Tushar Mehta]

I can tell you what it is doing, but not how or why.

The data set below is in A2:B7.

x y
2
4 0.0214
8 0.0232
12 0.0643
16 0.0778
20 0.1025

In XL2002 (and XL97), =SLOPE(B2:B7,A2:A7) returns 0.00542
(and INTERCEPT(...) -0.0072)

In XL2003 the corresponding results are 0.004987 and 0.006307

If one were to use this estimate of slope and intercept to calculate y-
values for the given x-values, the results are consistently higher than
the actual values as shown below.

x y y-calc
2 0.016281
4 0.0214 0.026255
8 0.0232 0.046203
12 0.0643 0.066152
16 0.0778 0.0861
20 0.1025 0.106049

XL2003 is replacing the missing y-value with the result of the FORECAST
function applied to the same data set (i.e.,
Y(2) =FORECAST(2,B2:B7,A2:A7). If this were the actual value of Y(2),
the slope and intercept would be correct.

This, in itself, is an error since the documentation from XL97 to
Xl2003 consistently indicates that missing values, text, and logical
values are ignored.

OK, so if SLOPE and INTERCEPT are using the FORECAST function, what
does it do?

Given the results above, I expected something quite bizarre. But, off-
hand, I cannot figure out what. =FORECAST(2,B2:B7,A2:A7) returns
0.016281. If one were to ignore the empty cell and then use forecast,
as in =FORECAST(2,B3:B7,A3:A7), the result is 0.00364. So, how it gets
0.016281 is very unclear.

OK. Final check. If Y(2) were not empty, what value would it have to
have to actually get the FORECAST value we get? With the help of Goal
Seek, turns out to be 0.031603.

I'd always thought that SLOPE and INTERCEPT simply indexed into the
result of LINEST, and FORECAST used SLOPE and INTERCEPT for its work.
But, LINEST consistently (97 to 2003) returns #VALUE! if any element is
missing. So, speculation: in 2003, SLOPE calls FORECAST for missing
data and FORECAST calls SLOPE. Luckily, or unluckily (?), their
iterative and recursive calls converge to a common solution and the
result is what we get.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Harlan Grove]

OK, so if SLOPE and INTERCEPT are using the FORECAST function, what
does it do?

Given the results above, I expected something quite bizarre. But, off-
hand, I cannot figure out what. =FORECAST(2,B2:B7,A2:A7) returns
0.016281. If one were to ignore the empty cell and then use forecast,
as in =FORECAST(2,B3:B7,A3:A7), the result is 0.00364. So, how it gets
0.016281 is very unclear.

....

Given that SLOPE is the only one of the three functions - FORECAST,
INTERCEPT and SLOPE - that could be calculated without needing the results
from the others, I'd bet that SLOPE and FORECAST both call INTERCEPT. If
INTERCEPT is now screwed up, er, inconsistent with previous functionality,
it's no surprise that SLOPE and FORECAST would also be screwed up.

Anyway, I'd focus on INTERCEPT rather than the others.

I'd always thought that SLOPE and INTERCEPT simply indexed into the
result of LINEST, and FORECAST used SLOPE and INTERCEPT for its work.

I'd guess you were right about the latter, but I doubt the former. LINEST is
overkill for SLOPE and INTERCEPT, which both assume a *single* independent
variable, so I think online help gives their actual implementation (at least
pre-2003 improvements).

. . . So, speculation: in 2003, SLOPE calls FORECAST for missing
data and FORECAST calls SLOPE. Luckily, or unluckily (?), their
iterative and recursive calls converge to a common solution and the
result is what we get.

I really doubt this.

In 2003, with SLOPE returning 0.004987,

=AVERAGE(B2:B7)-0.004987*AVERAGE(A2:A7)

returns 0.006307667, which to me is close enough to INTERCEPT's result of
0.006307 unless the SLOPE figure of 0.004987 is an exact rather than rounded
result. In other words, it looks to me like there's no proof that INTERCEPT
has changed from previous versions.

In previous versions, SLOPE's result is equal to the result from

=(COUNT(A3:A7)*SUMPRODUCT(A3:A7,B3:B7)-SUM(A3:A7)*SUM(B3:B7))
/(COUNT(A3:A7)*SUMPRODUCT(A3:A7,A3:A7)-SUM(A3:A7)^2)

Note that this excludes A2:B2.

It's possible in 2003 that FORECAST, SLOPE and INTERCEPT now call LINEST. I
don't have Excel 2003. Would anyone who does care to post the results from

=LINEST(B3:B7,A3:A7,1,1)

and confirm that

=LINEST(B2:B7,A2:A7,1,1)

returns errors using Tushar's sample data in A2:B7,

2
4 0.0214
8 0.0232
12 0.0643
16 0.0778
20 0.1025

I'm just not convinced that Excel is replacing blank cells with first pass
fitted values rather than that the new, inconsistent results are due to the
new algorithm counting the X and Y ranges differently than previous
versions.

 

[Jerry W. Lewis]

x y y-calc
2 0.016281
4 0.0214 0.026255
8 0.0232 0.046203
12 0.0643 0.066152
16 0.0778 0.0861
20 0.1025 0.106049

XL2003 is replacing the missing y-value with the result of the FORECAST
function applied to the same data set (i.e.,
Y(2) =FORECAST(2,B2:B7,A2:A7). If this were the actual value of Y(2),
the slope and intercept would be correct.

If that were the algorithm, the the slope and intercept would still
return the same values as the previous versions. Assuming that the
input data are given to full precision, I cannot simultaneously produce
a slope and an intercept that both round to the reported value simply by
replacing the missing y value with a single number. Consequently I
remain puzzled as to what calculation is actually being performed.

Jerry

 

[Harlan Grove]

...
...

. . . Assuming that the
input data are given to full precision, I cannot simultaneously produce
a slope and an intercept that both round to the reported value simply by
replacing the missing y value with a single number. . . .

As I tried to point out, it's possible if not likely that INTERCEPT hasn't
changed. If the new SLOPE function's ressult were passed to INTERCEPT, then the
following formula which reproduces the INTERCEPT function's result,

=AVERAGE(Y)-SLOPE(Y,X)*AVERAGE(X)

would give the new (Excel 2003) INTERCEPT function's result, at least within
likely rounding error. In other words, it's quite possible the change in
functionality is limited to SLOPE. But it's still a mystery how SLOPE works.

 

[Jerry W. Lewis]

I believe that Excel 2003 is calculating slope and intercept with the
following formulas:

a = average over non-missing x of x
b = average over non-missing y of y
xy = sum over non-missing x-y pairs of (x-a)*(y-b)
xx = sum over non-missing x of (x-a)^2
slope = xy/xx
intercept = b - a*slope

The formulas are correct whenever both x and y are missing when either
is missing, but wrong if there are any unmatched missing values, because
a, b, and xx treat missing values differently than xy.

Jerry

 

[Harlan Grove]

I believe that Excel 2003 is calculating slope and intercept with the
following formulas:

a = average over non-missing x of x
b = average over non-missing y of y
xy = sum over non-missing x-y pairs of (x-a)*(y-b)
xx = sum over non-missing x of (x-a)^2
slope = xy/xx
intercept = b - a*slope

The formulas are correct whenever both x and y are missing when either
is missing, but wrong if there are any unmatched missing values, because
a, b, and xx treat missing values differently than xy.

...

Looks like you're right. Picky: given Tushar's reported results from XL2003, xx
and xy seem to have consistent treatment of missing values; a and b could differ
in missing value treatment from each other as well as from xx and yy.

Putting this in equivalent formula terms, XL2003 SLOPE and INTERCEPT appear to
be equivalent to

slope:
=SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),X-AVERAGE(X),Y-AVERAGE(Y))
/SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),(X-AVERAGE(X))^2)

intercept:
AVERAGE(Y)-slope*AVERAGE(X)

Note: no apparent change in INTERCEPT's implementation. The pre-XL2003 SLOPE is
closer to

CommonXmean:
=SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),X)/SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y))

CommonYmean:
=SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),Y)/SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y))

slope:
=SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),X-CommonXmean,Y-CommonYmean)
/SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),(X-CommonXmean)^2)

or in full

=SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),
X-SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),X)/SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y)),
Y-SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),Y)/SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y)))
/SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),
(X-SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),X)/SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y)))^2)


So the new functionality in XL2003 does looks like a bug in the SLOPE function
and, indirectly, any other function that calls the SLOPE function, e.g.,
INTERCEPT and FORECAST. At least the second and third slope formulas above as
well as the intercept formula could be used to produce results consistent with
XL2002 and prior.


Looks like Microsoft is still trying to use performance hacks at the cost of
accuracy. Given the forgoing, SLOPE needs to loop through both the X and Y
variables twice - the first time calculating the means, and the second time
summing xx and xy. The second loop requires coordinated iteration through X and
Y, and that takes more time to do correctly than just iterating through each one
separately. So anyone want to bet that Microsoft didn't bother to coordinate
iterations in the first, mean calculation loop(s)?

I'm going to make an educated guess (since I don't have XL2003) that with the
following data (using underscores to indicate blanks)

X Y
2 _
4 0.0214
8 0.0232
_ 0.0643
16 0.0778
20 0.1025

XL2003 gives slope = 0.004854 and intercept = 0.009301, both to 4 significant
digits. In XL97, slope = 0.005420 and intercept = -0.008815. The first slope
formula above gives 0.004854, while the second and third slope formulas above as
well as XL97's SLOPE function give 0.005420.

This being the case, is anyone using XL2003 and a prior version willing to
compare LINEST results based on Y and multiple X variables with blank cells in
noncorresponding observations? Can you do that Jerry?

 

[Jerry W. Lewis]

...

..

Looks like you're right. Picky: given Tushar's reported results from XL2003, xx
and xy seem to have consistent treatment of missing values; a and b could differ
in missing value treatment from each other as well as from xx and yy.

Yes, I misspoke, xx is sum over non-missing x-y pairs

Putting this in equivalent formula terms, XL2003 SLOPE and INTERCEPT appear to
be equivalent to

slope:
=SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),X-AVERAGE(X),Y-AVERAGE(Y))
/SUMPRODUCT(ISNUMBER(X)*ISNUMBER(Y),(X-AVERAGE(X))^2)

intercept:
AVERAGE(Y)-slope*AVERAGE(X)

Agreed; I independently arrived at the same formulas today (but didn't
have newsgroup access to follow up).

Note: no apparent change in INTERCEPT's implementation.

Picky: INTERCEPT is using different averages than in previous versions,
but I agree that it is using what it thinks are the appropriate averages
in the same way as before. Thus it is possible for INTERCEPT to be
wrong (with missing values), even if it happens that SLOPE gives the
correct value by chance.

I also verified that this understanding of what 2003 does to SLOPE and
INTERCEPT explains what I think Jonathan Marder intended as his example in
http://groups.google.com/[email protected]
(it reproduces reported forecasts for x=0, 1, and 4; I think the
reported forecast for x=2 is actually for x=3, and the actual forecast
for x=2 was not reported by mistake).

The RSQ values in this OPs examples are correct, and would not be if
this bug contaminated the RSQ calculations. Presumably RSQ now uses
CORREL^2 and presumably CORREL was not "improved". Hopefully PEARSON
now uses CORREL instead of a new coding.

*****************************
To anyone who has Excel 2003:
*****************************

It would be interesting to see whether STEYX has been messed up too, if
someone would report 2003 STEYX results for the examples in this thread.

To verify that y is treated consistently with x, it would also be
interesting to see all calcs for the following modification of this
thread's examples

x y
2 0.0066
0.0214
8 0.0232
12 0.0643
16 0.0778
20 0.1025

Jerry

 

[Harlan Grove]

Picky: INTERCEPT is using different averages than in previous versions,
but I agree that it is using what it thinks are the appropriate averages
in the same way as before. Thus it is possible for INTERCEPT to be
wrong (with missing values), even if it happens that SLOPE gives the
correct value by chance.

...

You're right.

The RSQ values in this OPs examples are correct, and would not be if
this bug contaminated the RSQ calculations. Presumably RSQ now uses
CORREL^2 and presumably CORREL was not "improved". Hopefully PEARSON
now uses CORREL instead of a new coding.

*****************************
To anyone who has Excel 2003:
*****************************

It would be interesting to see whether STEYX has been messed up too, if
someone would report 2003 STEYX results for the examples in this thread.

...

Any statistic involving multiple random variables that could be more accurately
calculated by calculating means first then working with crossproducts of
deviations from those means is now suspect. All should be tested.

So much for regression testing when function results are expected to change.

 

[Harlan Grove]

...
...

*****************************
To anyone who has Excel 2003:
*****************************

...

Here are ranges and formula results from Excel 97 SR-2. I'll test these under
Excel 2000 SP-3 tonight at home (so not for several hours). Interesting to see
what the results would be under Excel 2002 and 2003.


X_0 Y_0
2 0.0066
4 0.0214
8 0.0232
12 0.0643
16 0.0778
20 0.1025

0.00531863 =SLOPE(Y_0,X_0)
-0.005659178 =INTERCEPT(Y_0,X_0)
0.215700 =COVAR(Y_0,X_0)
0.981150036 =CORREL(Y_0,X_0)
0.981150036 =PEARSON(Y_0,X_0)
0.962655393 =RSQ(Y_0,X_0)
0.008170509 =STEYX(Y_0,X_0)
____________________________________

X_1 Y_1
2 0.0066
0.0214
8 0.0232
12 0.0643
16 0.0778
20 0.1025

0.005543852 =SLOPE(Y_1,X_1)
-0.009428689 =INTERCEPT(Y_1,X_1)
0.216432 =COVAR(Y_1,X_1)
0.982393111 =CORREL(Y_1,X_1)
0.982393111 =PEARSON(Y_1,X_1)
0.965096224 =RSQ(Y_1,X_1)
0.008504364 =STEYX(Y_1,X_1)
____________________________________

X_2 Y_2
2 0.0066
4 0.0214
8 0.0232
12
16 0.0778
20 0.1025

0.0052675 =SLOPE(Y_2,X_2)
-0.006375 =INTERCEPT(Y_2,X_2)
0.252840 =COVAR(Y_2,X_2)
0.983792079 =CORREL(Y_2,X_2)
0.983792079 =PEARSON(Y_2,X_2)
0.967846855 =RSQ(Y_2,X_2)
0.008587326 =STEYX(Y_2,X_2)
____________________________________

X_3 Y_3
2 0.0066
0.0214
8 0.0232
12
16 0.0778
20 0.1025

0.005525385 =SLOPE(Y_3,X_3)
-0.011016923 =INTERCEPT(Y_3,X_3)
0.2693625 =COVAR(Y_3,X_3)
0.987468392 =CORREL(Y_3,X_3)
0.987468392 =PEARSON(Y_3,X_3)
0.975093826 =RSQ(Y_3,X_3)
0.008719571 =STEYX(Y_3,X_3)

 

[Harlan Grove]

Here are the results for Excel 97 (leftmost) and Excel 2000 (middle) along
with the common formula (rightmost). Underscores denote blank cells.


X_0 Y_0
2 0.0066
4 0.0214
8 0.0232
12 0.0643
16 0.0778
20 0.1025

0.00531863 0.00531863 =SLOPE(Y_0,X_0)
-0.005659178 -0.005659178 =INTERCEPT(Y_0,X_0)
0.2157 0.2157 =COVAR(Y_0,X_0)
0.981150036 0.981150036 =CORREL(Y_0,X_0)
0.981150036 0.981150036 =PEARSON(Y_0,X_0)
0.962655393 0.962655393 =RSQ(Y_0,X_0)
0.008170509 0.008170509 =STEYX(Y_0,X_0)
____________________________________

X_1 Y_1
2 0.0066
_ 0.0214
8 0.0232
12 0.0643
16 0.0778
20 0.1025

0.005543852 0.005543852 =SLOPE(Y_1,X_1)
-0.009428689 -0.009428689 =INTERCEPT(Y_1,X_1)
0.216432 0.216432 =COVAR(Y_1,X_1)
0.982393111 0.982393111 =CORREL(Y_1,X_1)
0.982393111 0.982393111 =PEARSON(Y_1,X_1)
0.965096224 0.965096224 =RSQ(Y_1,X_1)
0.008504364 0.008504364 =STEYX(Y_1,X_1)
____________________________________

X_2 Y_2
2 0.0066
4 0.0214
8 0.0232
12 _
16 0.0778
20 0.1025

0.0052675 0.0052675 =SLOPE(Y_2,X_2)
-0.006375 -0.006375 =INTERCEPT(Y_2,X_2)
0.25284 0.25284 =COVAR(Y_2,X_2)
0.983792079 0.983792079 =CORREL(Y_2,X_2)
0.983792079 0.983792079 =PEARSON(Y_2,X_2)
0.967846855 0.967846855 =RSQ(Y_2,X_2)
0.008587326 0.008587326 =STEYX(Y_2,X_2)
____________________________________

X_3 Y_3
2 0.0066
_ 0.0214
8 0.0232
12 _
16 0.0778
20 0.1025

0.005525385 0.005525385 =SLOPE(Y_3,X_3)
-0.011016923 -0.011016923 =INTERCEPT(Y_3,X_3)
0.2693625 0.2693625 =COVAR(Y_3,X_3)
0.987468392 0.987468392 =CORREL(Y_3,X_3)
0.987468392 0.987468392 =PEARSON(Y_3,X_3)
0.975093826 0.975093826 =RSQ(Y_3,X_3)
0.008719571 0.008719571 =STEYX(Y_3,X_3)

 

[Peo Sjoblom]

Harlan,

I have 2003 at home and I will have a look when I get home tonight, would
you care to email your test sheet
privately or post it somewhere for download so I can use the same data
without typing too much?

(e-mail address removed)


Regards,

Peo Sjoblom

 

[Harlan Grove]

I have 2003 at home and I will have a look when I get home tonight, would
you care to email your test sheet
privately or post it somewhere for download so I can use the same data
without typing too much?

ftp://members.aol.com/hrlngrv/2rvtest.xls

 

[Peo Sjoblom]

Thanks, result sent privately (I had a 2003 on my laptop)

 

[Harlan Grove]

Thanks, result sent privately (I had a 2003 on my laptop)

Where'd you send it? I haven't received it through AOL yet.

 

[Peo Sjoblom]

I sent it to your aol address that's in your posts, anyway here is the data,
excel 2002 was identical to 97 and 2000
hopw this comes out unscrambled

XL97 XL2000 XL2002 XL2003
Formula

X_0 Y_0
2 0.0066
4 0.0214
8 0.0232
12 0.0643
16 0.0778
20 0.1025

0.00531863 0.00531863 0.00531863 0.00531863
=SLOPE(Y_0,X_0)
-0.005659178 -0.005659178 -0.005659178 -0.005659178
=INTERCEPT(Y_0,X_0)
0.2157 0.2157 0.2157 0.2157
=COVAR(Y_0,X_0)
0.981150036 0.981150036 0.981150036 0.981150036
=CORREL(Y_0,X_0)
0.981150036 0.981150036 0.981150036 0.981150036
=PEARSON(Y_0,X_0)
0.962655393 0.962655393 0.962655393 0.962655393
=RSQ(Y_0,X_0)
0.008170509 0.008170509 0.008170509 0.008170509
=STEYX(Y_0,X_0)
____________________________________

X_1 Y_1
2 0.0066
0.0214
8 0.0232
12 0.0643
16 0.0778
20 0.1025

0.005543852 0.005543852 0.005543852 0.005543852
=SLOPE(Y_1,X_1)
-0.009428689 -0.009428689 -0.009428689 -0.015008689
=INTERCEPT(Y_1,X_1)
0.216432 0.216432 0.216432 0.216432
=COVAR(Y_1,X_1)
0.982393111 0.982393111 0.982393111 0.982393111
=CORREL(Y_1,X_1)
0.982393111 0.982393111 0.982393111 0.982393111
=PEARSON(Y_1,X_1)
0.965096224 0.965096224 0.965096224 0.965096224
=RSQ(Y_1,X_1)
0.008504364 0.008504364 0.008504364 0.011145322
=STEYX(Y_1,X_1)
____________________________________

X_2 Y_2
2 0.0066
4 0.0214
8 0.0232
12
16 0.0778
20 0.1025

0.0052675 0.0052675 0.0052675 0.005255335
=SLOPE(Y_2,X_2)
-0.006375 -0.006375 -0.006375 -0.008005127
=INTERCEPT(Y_2,X_2)
0.25284 0.25284 0.25284 0.25284
=COVAR(Y_2,X_2)
0.983792079 0.983792079 0.983792079 0.983792079
=CORREL(Y_2,X_2)
0.983792079 0.983792079 0.983792079 0.983792079
=PEARSON(Y_2,X_2)
0.967846855 0.967846855 0.967846855 0.967846855
=RSQ(Y_2,X_2)
0.008587326 0.008587326 0.008587326 0.008880797
=STEYX(Y_2,X_2)
____________________________________ ____________________________________

X_3 Y_3
2 0.0066
0.0214
8 0.0232
12
16 0.0778
20 0.1025

0.005525385 0.005525385 0.005525385 0.005511485
=SLOPE(Y_3,X_3)
-0.011016923 -0.011016923 -0.011016923 -0.017633224
=INTERCEPT(Y_3,X_3)
0.2693625 0.2693625 0.2693625 0.2693625
=COVAR(Y_3,X_3)
0.987468392 0.987468392 0.987468392 0.987468392
=CORREL(Y_3,X_3)
0.987468392 0.987468392 0.987468392 0.987468392
=PEARSON(Y_3,X_3)
0.975093826 0.975093826 0.975093826 0.975093826
=RSQ(Y_3,X_3)
0.008719571 0.008719571 0.008719571 0.012956934
=STEYX(Y_3,X_3)