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

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.

-

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).

-
 I was guessing that there's no other solution. As the ITU Rec says, I should just take the mean and compute CI: "The average of the opinion scores should be calculated for each test condition. Confidence limits should be evaluated." - so I'll go with that. Thanks! – slhck Mar 29 '11 at 19:38 @slhck There are other solutions. For instance, you could construe the responses as rounded versions of continuous values. If the rounding is to the nearest integer, use $\sqrt{1/12}$ in place of the standard deviation when all the values are the same. (This also has its problems, but it at least acknowledges the potential for sample variation in the mean response.) – whuber♦ Mar 29 '11 at 20:28

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.

-
Thing is, I can't assume anybody rounded. The calculation of MOS is standardized (see above) and therefore I must take the CI using the data I have: "The average of the opinion scores should be calculated for each test condition. Confidence limits should be evaluated." – slhck Mar 29 '11 at 19:36
If an authority tells you what to do then you have little choice. People, however, very rarely think exactly the same. It's more that the 1-2-3-4-5 scale did not have the resolution to capture the variation among the opinions. You will get a confidence interval of zero width, understating the inaccuracy. If I had to follow the standardized calculation I would put a remark into my report about this methodical weakness. – GaBorgulya Mar 29 '11 at 20:01
+1 Nice conceptual approach. You don't need a simulation, though: you can directly compute the chance that 8 independent draws from a normal(0, $\sigma$) distribution all lie within $1/2$ of 0 and then find $\sigma$ for which that chance is fairly high; 95%, say (solution: .183351). – whuber Mar 29 '11 at 23:29

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?

-
Why shouldn't it be a normal distribution? You are right about the scale. I added some clarification to my original question, maybe that helps. – slhck Mar 29 '11 at 18:32
I you are sampling from a normal distribution the probability of getting two equal values is zero. If you use reasonable rounding, you may get a few equal values in a large sample. – GaBorgulya Mar 29 '11 at 18:55

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.

-
 The 95% comes from the desired confidence level, so it has already been specified in the problem. The burden on anyone suggesting an alternative confidence interval procedure is to demonstrate that their proposal guarantees 95% (or whatever is desired) coverage of the true mean--no matter what the true mean might happen to be--and that it does so efficiently: that is, its minimum coverage really is 95% or close to it. Although your proposal might have this property, it's not apparent that it does. – whuber♦ Mar 30 '11 at 4:35