CALC - linear regression - function linest/regr.lin - return values - correlation diagrees with Excel results

users
#1

I had to help a friend in solving a very simple problem, namely finding
the linear best fit of a series of 50 data. I generally use LibreOffice
with linux, my friend uses Excel with windows. As far as we fitted the
data using the standard f(x) = a *x + b function everything was ok (Excel
results and LibreOffice results were in complete agreement. Just to be sure
computations were made using the matrix function regrl.lin () [i think
that the english name is linest() ], and compared to slope(), intercept(),
rq() , err.std.ys() etc.

The problem arose when we tried to fit with using the f(x) = a °x
(obviously after a change of xy axis so that Y0,X0 becomes 0,0 ). While
the results for the slope were in agreement there was a strong diagreement
in the 'correlation' ( R2) value.

1) in both cases (Excel and LibreOffice) the value changes; I suppose that
this means that the value is not the Pearson correlation (which should be
invariant), but probably
    the value represents how much of the variations in y values is
explained by the model
2) if so the correlation given by Excel is correct (I have done computation
explicitly) and Libre Office is in complete error (Pearson -> 0.87, R2 for
single parameter fit from Excel ->0.74, R2 from Libre.Office ->0.98

According to the on lne manual the returned value R2 shold be RQ(), that is
the __invariant__ Pearson. I am really lost, I do not understand what
the linest() // regr.lin() function returns. I have made some google
search but I did nor find anything covring this topic.

Any help/suggestion/pointer welcome

f.mencaraglia
cell 333 649 89 62