function cell range limitations

[AXA]

In using the CORREL(correlation) function, which correlates data over 2 cell
ranges, I have found that if the number of cells in each range is 14 or less,
the results are valid, for both whole and decimal numbers. However, if the
number of cells in each range is 15 or greater, the results are NOT valid for
decimals, but remain valid for whole numbers. Note that for CORREL both
ranges must have the same number of cells.
Any thoughts on what is being missed here ?

 

[Michael R Middleton]

AXA -

Three issues:

(1) Which version of Excel?

(2) Please provide data (as a list of values in a message, not as an
attachment) with instructions for replicating results that are not valid.

(3) Have you looked at an XY (Scatter) chart of the data to verify that the
CORREL measure of linear (straight line) relationship is appropriate?

- Mike

www.mikemiddleton.com

+++++++++++++++++++++++

 

[AXA]

Hi Mike,

Thanks for the reply.

In answer to your queries:

(1) Excel 2003(11.6355.6360) SP1

(2) Use 1.304 for, say A2:A15, and use 1.8882 for, say B2:B15. CORREL
produces #DIV/0! , which is a 'valid' result since division by 0 does occur.
However, if the range is changed to A2:A16 and B2:B16, that is to 15 cells
from 14 cells, then Correl produces -1 , which is not valid given the data.
#DIV/0! should still be the result.

Note that -1 can be a valid CORREL result for data pairs which exhibit an
inverse relationship, but that is not the case here. And as you are also
aware CORREL results are from -1 to 1 inclusive.

Note that if whole numbers are used, then 15 vs 14 cells is not an issue as
#DIV/0! is the result with both 15 and 14 cells.

The dilemna here would seem to be in the use of decimals. Of course,
results involving division by 0 can be difficult to draw conclusions from,
and that may be what is going on here. It was just the 15 vs 14 cells that
I'm wondering about.

With all functions that I use I try to test the extremes in order to draw
inferences concerning function operations.

Eventually I will be using hundreds of pairs of data with CORREL, and
although the extremes will probably never be realized, I just wanted to
examine the behavior of the function, CORREL, at those extremes.

(3) I did look at an XY (Scatter) chart for the above data, but the charts,
given the data, were not meaningful.


Many thanks Mike for your response to this posting. Also, I went to your
website and you certainly would be qualified where the above is concerned.

AXA

 

[Michael R Middleton]

AXA -

I don't have Excel 2003 handy to check, but using Excel 2002 I was not able
to replicate your erroneous -1 value for CORREL when the range is extended
to include empty cells; CORREL continued to return #DIV/0! in Excel 2002.

My limited experience is to focus on first "looking at the data" using an XY
(Scatter) chart before choosing appropriate numerical summary measures, so I
don't think I can be of assistance when the chart is "not meaningful" in
your situation.

Have you considered using COVAR instead of CORREL?

Also, the super-statistician and numerical-methods experts may want to help
you with your problem. I suggest you search Google Groups for messages by
Jerry W. Lewis and others. For example, if you enter "excel correl jerry w.
lewis" without the quotes for a Google Group Search, you may find some
relevant discussions.

- Mike

+++++++++++++++++++++++