As a possibly useful preview, enter 1.9 into A15
In A14 enter the formula
=A15+2^-51
In A15 enter the formula
=A15-2^-51
Copy cell A14 and paste over A1:A13
Copy cell A16 and paste over A17:A30
Format A1:A16 as numeric with 15 decimal places
This illustrates that (as documented) Excel will only display 15 digits
(14 decimal places with these numbers), yet there are 23 distinct binary
numbers that all display as 1.90000000000000
In your case your cell formulas resulted in at least two of these 23
distinct values, hence the resulting nonzero correlation.
Jerry
Jerry W. Lewis wrote:
I presume then that B4:B6 are calculated results. Because of decimal/
binary conversions, etc. it is possible to have slight differences
between calculated results that cannot be displayed. If you will post
(text only, please, no attachments) the formulas and the contents of
the referenced cells, I will endeavor to explain why these slight
differences are not arithmetic errors. Meanwhile, you will find that
at least one of the following formulae are not zero
=(B4-B5)
=(B4-B6)
=(B5-B6)
The parentheses in these formulas are required to see the differences
when the values are equal to 15 decimal places.
In cases such as this, rounding the results in B4:B6 is the only way to
get the expected result.
Jerry
Diane wrote:
If I increase B4:B6 to show 14 decimal places, the values will only be
1.90000000000000 - Why would that make a difference?
-----Original Message-----
See Help for CORREL worksheet function. The denominator
is the product
of standard deviations for x and y. The standard
deviation of B12:B14
is zero ...
Format B4:B6 to show 14 decimal places. You will find
that they are not
all equal, hence the standard deviation is small, but
not zero.
Jerry
Diane wrote:
I have a formula in cell C7, the formula is: CORREL(B4:B6,C4:C6)
The cells have the follwing values:
B4: 1.90
B5: 1.90
B6: 1.90
C4: 2.13
C5: 1.83
C6: 3.02
Cell C7 returns: 0.000
Here's where the problem comes in.
The formula in cell C15 is: CORREL(B12:B14,C12:C14)
The cells have the follwing values:
B12: 9.50
B13: 9.50
B14: 9.50
C12: 9.56
C13: 10.26
C13: 10.16
Cell C15 returns the error: #DIV/0!
Why is the formula producing the #DIV/0! error in the second set and
not the first or vice versa?
ANY help would be greatly appreciated.
Thanks, Diane
|