SOLVED

CORREL function returning a result >1 in office365 for mac

%3CLINGO-SUB%20id%3D%22lingo-sub-1805857%22%20slang%3D%22en-US%22%3ECORREL%20function%20returning%20a%20result%20%26gt%3B1%20in%20office365%20for%20mac%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1805857%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20case%20of%20CORREL(array1%2Carray2)%20function%20on%20office365%20for%20mac%20that%20return%20a%20result%20greater%20than%201%2C%20which%20is%20normally%20impossible%2C%20bug%20%3F%3F%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1805857%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1806382%22%20slang%3D%22en-US%22%3ERe%3A%20CORREL%20function%20returning%20a%20result%20%26gt%3B1%20in%20office365%20for%20mac%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1806382%22%20slang%3D%22en-US%22%3ECORREL%20itself%20is%20returning%20a%20value%20less%20than%201.%3CBR%20%2F%3EThe%20formulas%20that%20return%20greater%20than%201%20are%3A%3CBR%20%2F%3EN9%3A%20%3D0.823345142%2BCORREL(E1%3AE52%2CD1%3AD52)%3CBR%20%2F%3EX9%3A%20%3D0.823345142%2BCORREL(E1%3AE52%2CB1%3AB52)%3CBR%20%2F%3EThey%20return%201.61867951024453%20and%201.6288521538516%20respectively.%3CBR%20%2F%3ESo%2C%20subtracting%200.823345142%20or%20using%20F9%20in%20the%20Formula%20Bar%2C%20CORREL%20returns%200.79533436824453%20and%200.805507011851603%20respectively.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1810418%22%20slang%3D%22en-US%22%3ERe%3A%20CORREL%20function%20returning%20a%20result%20%26gt%3B1%20in%20office365%20for%20mac%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1810418%22%20slang%3D%22en-US%22%3EThank%20you%2C%20didn't%20notice%20the%20change%20in%20the%20formula.%20Now%20the%20good%20question%20is%20why%20(I%20didn't).%3C%2FLINGO-BODY%3E
New Contributor

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

3 Replies
Best Response confirmed by MHL15 (New Contributor)
Solution

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.

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

@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)