Oct 21 2020 01:52 PM
Hi,
I have a case of CORREL(array1,array2) function on office365 for mac that return a result greater than 1, which is normally impossible, bug ????
Oct 21 2020 04:25 PM - edited Oct 21 2020 04:29 PM
SolutionCORREL itself is returning a value less than 1.
The formulas that return greater than 1 are:
N9: =0.823345142+CORREL(E1:E52,D1:D52)
X9: =0.823345142+CORREL(E1:E52,B1:B52)
They return 1.61867951024453 and 1.6288521538516 respectively.
So, subtracting 0.823345142 or using F9 in the Formula Bar, CORREL returns 0.79533436824453 and 0.805507011851603 respectively.
-----
PS.... Those are the values that I see when I open your workbook in Excel 2010 on a PC. If you see different results with Office 365 for Mac, I suggest that you add cells that calculate only =CORREL(...), and show us the results, displaying 15 significant digits.
Oct 22 2020 02:24 PM
Oct 22 2020 02:29 PM
@MHL15 Re: ``Now the good question is why (I didn't).``
Sorry, can't answer that. (wink) Seriously, been there, done that. (sigh) For me, it's called "a senior moment". (smile)
Oct 21 2020 04:25 PM - edited Oct 21 2020 04:29 PM
SolutionCORREL itself is returning a value less than 1.
The formulas that return greater than 1 are:
N9: =0.823345142+CORREL(E1:E52,D1:D52)
X9: =0.823345142+CORREL(E1:E52,B1:B52)
They return 1.61867951024453 and 1.6288521538516 respectively.
So, subtracting 0.823345142 or using F9 in the Formula Bar, CORREL returns 0.79533436824453 and 0.805507011851603 respectively.
-----
PS.... Those are the values that I see when I open your workbook in Excel 2010 on a PC. If you see different results with Office 365 for Mac, I suggest that you add cells that calculate only =CORREL(...), and show us the results, displaying 15 significant digits.