Per Help for CORREL and DEVSQ,

=CORREL(B4:B6,C4:C6)

is equivalent to

=COVAR(B4:B6,C4:C6) / ( SQRT(DEVSQ(B4:B6)/COUNT(B4:B6)) *

SQRT(DEVSQ(C4:C6)/COUNT(C4:C6)) )

=DEVSQ(B12:B14) is zero but =DEVSQ(B4:B6) is not zero, hence the

difference in CORREL results.

=DEVSQ(B4:B6) is not zero because =(B4-AVERAGE(B4:B6)) is not zero.

That can happen when all of the values are identical, because computer

math is done to finite precision. Imagine that you had a decimal

computer that carried only 4 figures in calculations. In that case,

SUM(0.4444,0.4444,0.4444) would be 1.333 since 1.3332 would require a

5th figure. Thus AVERAGE(0.4444,0.4444,0.4444) would be 0.4443 instead

of 0.4444 and hence (0.4444-AVERAGE(0.4444,0.4444,0.4444)) would not be

zero.

You are probably wondering how this is applicable, when your repeated

number is 1.9 since Excel advertises 15 digit precision. The reason is

that Excel (and almost all other general purpose hardware and software)

does binary math, not decimal math. Most decimal fractions (including

1.9) is a non-terminating binary fraction, just as 1/3 is a

non-terminating decimal fraction. The trailing bit pattern in the

binary approximation to 1.9 is such that there is lost information when

you add 3 copies of 1.9 (crossing 2 powers of 2) just as in my

hypothetical example. If you chase through the details of the IEEE 754

standard, you will find that =(B4-AVERAGE(B4:B6)) should equal

1/4503599627370496 which is 2.220446049250313080847263336181640625E-16.

Excel displays that difference as 2.22044604925031E-16, indicating that

it is performing the arithmetic correctly.

9.5 is exactly representable in binary (an integer plus a power of 2),

hence finite precision arithmetic matches the infinite precision

arithmetic that you would mentally do.

Jerry