As far as I know excel is able to calculate confidence intervals
Yes. But I think the point is: your question reflects a lack of
understanding of basic statistics. Perhaps you have heard of GIGO --
garbage in, garbage out. Excel can provide the operations. But if
you use them incorrectly, you will get incorrect results.
For example, Excel does have a CONFIDENCE() function. But you do not
provide sufficient statistical information to use it. And even if you
have that information, CONFIDENCE() would be the wrong function to use
in this case, IMHO.
1) How do I calculate confidence limits? I need confidence limits on density
indexes but they themeselves are just calculated as no. observed/km2.
Say the density index is 11.63 hares/km2 - what to do?
Confidence limits (aka a confidence interval) are boundaries around an
average, given a standard deviation of N data and a level of
confidence that you want to have. Basically, a CI says that we are x%
confident that the true average is the observed average +/- delta,
where "delta" is the confidence limit for that level of confidence.
It is unclear whether 11.63 hares/km2 is a single observation or an
average of several observations. If an average, you also need to know
the number of observations, and you need to know either the individual
observations that comprise the average or the standard deviations of
those observations.
2) Can confidence limits be calculated from total number of observed hares?
I observed 320 hares in spring and 310 in fall - can it be done at all?
"At all"? Yes. But with any reasonable degree of confidence? No,
not with only two observations.
Nonetheless, to use the two data points as an example, you might say
that you are 95% confident that the true average is AVERAGE(320,310)
plus or minus the result of CONFIDENCE(1-95%,STDEV(320,310),2).
That is really "wrong" (GIGO) for several reasons.
First, the construction and use of the confidence interval defined by
CONFIDENCE() is valid for a "normal distribution" of data. Two data
points are not sufficient to demonstrate that you have a "normal
distribution". (On the other hand, often we simply ass-u-me that the
data is "normally distributed" in a specific circumstance, based on
the historical nature of the circumstances.)
Second, two data points are not sufficient to construct a "normal
distribution". Theoretically, you need a minimum of 3 data points,
assuming that they are "normally distributed". But as a practical
matter, you need more.
Third, CONFIDENCE() is the wrong function to use in this case, IMHO.
It presumes that we know the true standard deviation of the averages
all possible groups of N observations. At best, we only know the
sample standard deviation of a relatively small number of N
observations. So, we should use "Student's t" values, not "z values",
to construct the confidence interval. For the above (insufficient)
example: TINV(1-95%,2-1)*STDEV(320,310)/SQRT(2-1). Note that for
this example, that is more than 9 times greater than the value
returned by CONFIDENCE().
Disclaimer: As I noted, the above is all about confidence intervals
for a "normal distribution", which is usually what people mean by that
term. In fact, confidence limits can be defined for other
distributions using different formulas.
