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" wrote:

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" wrote in message
...
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 ?