Excel's confidence interval function throws #NUM! when standard deviation is 0

Question

I have some samples from eight people who all gave the same answer to a question. Now, obviously the sample's mean is the answer all people gave, and the standard dev is 0. Excel throws a #NUM! error when I call the function

CONFIDENCE.T(0.05, K33, COUNTA(B33:I33))

where K33 is the standard dev (0).

What would be the correct interpretation of this? Can I even calculate a confidence interval?

For clarification: People are asked to give their opinion on a scale of (1, 2, 3, 4, 5), which is ordinal. Nevertheless, one always calculates the arithmetic mean of all judgements (according to ITU-T P.800, see also: Wikipedia), so that's why I also want to get a confidence interval.

Answer 1

This behavior is questionable but documented. The help for "confidence" states:

If standard_dev ≤ 0, CONFIDENCE returns the #NUM! error value. ... If we assume alpha equals 0.05, we need to calculate the area under the standard normal curve that equals (1 - alpha), or 95 percent. This value is ± 1.96. The confidence interval is therefore:

$$\bar{x} \pm 1.96\left(\frac{\sigma}{\sqrt{n}}\right).$$

(Yes, this is badly phrased, but that's a direct quote.)

To overcome these (somewhat artificial) limitations, compute the confidence limits yourself (according to this formula) as

=AVERAGE(X) + NORMSINV(1-0.05/2) * STDEV(X)/SQRT(COUNT(X))
=AVERAGE(X) - NORMSINV(1-0.05/2) * STDEV(X)/SQRT(COUNT(X))

where 'X' names a range containing your data (such as B33:I33) and '0.05' is $\alpha$ (the complement of the desired confidence), just as before. In your case, because STDEV(X) is 0, both limits will equal the mean. This is legitimate, although it has its own problems (because it almost surely fails to cover the true mean).

Answer 2

Let's assume all your 8 subjects chose to answer 3 on the (1, 2, 3, 4, 5) scale. Let's assume that their opinions were continuous in their minds, and they rounded it to the closest values of the scale.

This means that the original opinion of each subject was in the range $[2.5, 3.5)$.

> mean(replicate(1e5, diff(range(rnorm(8)))))
[1] 2.841661
> mean(replicate(1e5, diff(range(rnorm(8)))))
[1] 2.847447
> 1 / 2.845
[1] 0.3514938

The above simulation shows that if you take 8 samples from a normal distribution of sd 0.35 they will cover an interval of the approximate width of 1.

Thus in your population the sd is likely to be 0.35 or less. Rounding to one of 1, 2, 3, 4, 5 is not precise enough to measure the sd in this case.

Answer 3

If eight samples from a distribution are exactly the same it is probably not a normal distribution or you use rounding at a higher order of magnitude than of the standard deviation. Or are you calculating means on a numerically coded ordinal scale?

Answer 4

Let's suppose that you have a number of instances in which the average rating is 3. Each of these will have a variance -- if the raters all answered "3" then that variance will be zero. In such cases, why not use the average of the variances in which the average rating is 3 (including your 0 value)? This will give you a real number and a reasonable confidence interval. I would use median rather than mean to "average" the variances, since it is less subject to extremes (although extremes would be unlikely on a fixed 5 point scale).

Of course, you might decide that any average rating in some range (such as 2.5 to 3.499) counts as "3" in order to give you more values to average.

This procedure is simple and intuitive. I like whuber's approach as well, but then somebody is going to ask you "why 95%? why not some other %". You are less likely to get this question if you take a simple average.