Forum Discussion

Copper Contributor

Oct 21, 2020

Solved

CORREL function returning a result >1 in office365 for mac

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 ????

JoeUser2004
Oct 21, 2020
CORREL 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.

JoeUser2004

Bronze Contributor

Oct 21, 2020

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

MHL15

Copper Contributor

Oct 22, 2020

Thank you, didn't notice the change in the formula. Now the good question is why (I didn't).

JoeUser2004
Bronze Contributor
Oct 22, 2020
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)