Covariance Formula

[sforster]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Why does the Excel formula for covariance divide by n as opposed to n-1? Does anyone have any answer or work around? Thanks

 

[JE McGimpsey]

Why does the Excel formula for covariance divide by n as opposed to n-1? Does
anyone have any answer or work around? Thanks

XL calculates COVAR() as the covariance of of a population, just as it
does STDEVP() and VARP().

So why aren't there corresponding COVAR() and COVARP() functions?

Who knows?

Perhaps everyone involved in developing the functions back in the 70's
thought that population COVAR() would be enough for everyone's purposes.

As far as a workaround, one can tell just by a quick look at the
formula in XL Help that it's trivial to calculate the sample Covariance
- just multiply the population Covariance by

n / (n-1)

right? Just the reverse of how you'd estimate a population Covariance
from a sample Covariance...

Depending on your circumstances, then, the XL formula for sample
covariance might be something like:

=COVAR(arr1, arr2) * COUNT(arr1)/(COUNT(arr1)-1)

 

[sforster]

Thanks for the quick reply. I thought that multiplying by n/n-1 would be the best. I was wondering because I am teaching a beginning statistics class and we are using Excel as a tool for calculations. I know that it is going to be difficult for the students to learn new material, and learn Excel formulas at the same time. I just hope that they don't get too confused when I tell then that they need to adjust just this one formula. Regardless, thanks for your prompt reply.

 

[Corentin Cras-Méneur]

Thanks for the quick reply. I thought that multiplying by n/n-1 would be
the best. I was wondering because I am teaching a beginning statistics
class and we are using Excel as a tool for calculations. I know that it
is going to be difficult for the students to learn new material, and
learn Excel formulas at the same time. I just hope that they don't get
too confused when I tell then that they need to adjust just this one
formula. Regardless, thanks for your prompt reply.

There are many gaps and inconsistencies in the way Excel treats
statistics...
It doesn't have something as simple as SEM, no non-parametric test, etc.
I now use dedicated software to do my stats.


Corentin

 

[JE McGimpsey]

Thanks for the quick reply. I thought that multiplying by n/n-1 would be the
best. I was wondering because I am teaching a beginning statistics class and
we are using Excel as a tool for calculations. I know that it is going to be
difficult for the students to learn new material, and learn Excel formulas at
the same time. I just hope that they don't get too confused when I tell then
that they need to adjust just this one formula. Regardless, thanks for your
prompt reply.

As you've discovered, XL's statistics functions are inconsistent and
have been historically suspect, though they've improved much in the last
2 versions.

XL is NOT a good stats package - there are lots of others out there.

<rant>
PLEASE don't tell your students that they "need to adjust just this one
formula". Tell them they need to test and understand EVERY formula that
they use.

The BEST thing you can impart to your students, and something that I
don't see reflected out in the workplace, is that XL is simply a tool,
and, like all other tools, you need to be careful that you understand
how it works.

The wailing and gnashing of teeth among my clients when it was
discovered that the ATP Histogram/ANOVA/etc. wizards were not in XL08
was phenomenal, and depressing, to watch.

I made a casual suggestion to one of the first to complain that they
simply create one from scratch, since the ATP wizard just automated the
built-in functions anyway.

From the look she gave me, you'd have thought I had two heads!

I've since found that nearly everyone who's complained DOESN'T KNOW HOW
TO CREATE A HISTOGRAM, even conceptually!

Some of these people are making serious business and public policy
decisions, affecting lots of money and their own and other people's
jobs, simply trusting that "if XL gives an answer, it must be right".

It's this ignorance of the underlying theory that leads to utterly bad
decisions in so many areas.
</rant>