Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am using MS-Excel for the first time to evaluate Correlation. To learn it I made two sequences of one as shown below:

enter image description here

I understand I have selected range correctly (as can be seen in the correlation dialog box), but still I am getting #DIV/0! (I am expecting 1 here as the sequences are 100% similar) as can be seen here:

Appreciate your help in solving the issue.

enter image description here

share|improve this question

migrated from stackoverflow.com Jan 3 '13 at 6:08

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 1 down vote accepted

From the documentation of CORREL:

If either array1 or array2 is empty, or if s (the standard deviation) of their values equals zero, CORREL returns the #DIV/0! error value.

Both of your arrays have a standard deviation of zero. Try changing at least one of the values in each array to something other than one and computing the correlation coefficient again.

share|improve this answer
    
Incidentally, from your screenshot it looks like you are using the data analysis add-in (basing that on this), so while I am answering using CORREL, it seems likely a zero standard deviation will manifest the same problem with the other approach. Please let me know if that is not the case. –  David Alber Jan 1 '13 at 15:53
    
your understanding is correct, I made the changes and it worked perfectly well –  gpuguy Jan 2 '13 at 9:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.