3

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

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • 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, 2013 at 15:53
  • your understanding is correct, I made the changes and it worked perfectly well
    – gpuguy
    Jan 2, 2013 at 9:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .