Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

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

You must log in to answer this question.

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