Using CONFIDENCE function

Further to this issue, I have calculated the true confidence limits of the examples for the CONFIDENCE function provided in the Numbers Functions Browser. The results cause me to believe that the function is calculating the confidence intervals incorrectly, especially for small samples (n < 30 or so).

For normally-distributed data, the confidence limits are provided by t(s/SQRT n) in small samples (<30 or so), where t is Student's t with n-1 degrees of freedom, s is standard deviation, and n is sample size. As sample n increases, the t distribution increasingly approaches the normal distribution, so that by n >120 or so, the two distributions are virtually identical, and either t or z (the normal coefficient) can be used.

The Numbers Function Browser examples return the following results when the CONFIDENCE function is used (top line). The true confidence intervals as calculated by t*(s/SQRT n) are on the second line.

0.62, 0.52, 0.44, 0.36, 0.31 *Numbers CONFIDENCE function*
0.72, 0.58, 0.47, 0.37, 0.32 *t(s/SQRT n)*

Comparing only the 4 examples with the same alpha value (0.05), the same comparison would be as follows, with the n values and % error values below.

0.62, 0.44, 0.36, 0.31 *Numbers CONFIDENCE function*
0.72, 0.47, 0.37, 0.32 *t(s/SQRT n)*
10, 20, 30, 40 n
13.4, 6.4, 4.2, 3.1 *% error*

Note that the percentage errors decrease as sample n increases. For this reason I suspect that CONFIDENCE is using the normal distribution rather than the t distribution to calculate confidence intervals, which is only accurate for large sample sizes. Using the normal distribution for small sample n will give confidence intervals that are too small.

My conclusion is that the CONFIDENCE function in Numbers should be used to calculate confidence intervals only for sample sizes (n) > 120, although the function will give reasonably accurate results for samples with n > 60 or so. For smaller sample sizes, calculate the standard error, use Numbers' TINV function to get the t value, and calculate confidence intervals as t(s/SQRT n). Using this method will always be correct for normally-distributed data, no matter what the sample size.

F Shippey, thanks for the idea. I now have done that. with the suggestions that (1) users should be warned that the CONFIDENCE function is not accurate for small samples, and that the function, while accurate for large samples, could be made useful for all sample sizes by using Student's t.

All of this depends of course on my guess being correct -- that the CONFIDENCE algorithm uses the normal distribution to set confidence limits. I would still like to see someone well-versed in stats to check that claim. What I do know is that calculating confidence intervals from sample standard deviation, n and the appropriate t value always gives the correct answer for data that can reasonably be assumed to come from an underlying normally-distributed population. The CONFIDENCE function in Numbers does not seem to do that.

Jerry,

Thanks -- another good idea.

I don't have Excel or access to it, but there are other ways I can check its calculations. OpenOffice I don't use, but maybe I should. I will look into getting a copy and trying your idea.

I think the main issue is this. In my experience, most datasets for which confidence intervals are needed are quite small. You could probably argue that confidence intervals really are most useful for small datasets, because that is where precision concerns are the greatest. So it is precisely when accurate confidence intervals are most needed that unaware users are most likely to use the CONFIDENCE function, which as I say is seriously inaccurate for small samples. As I also noted, there is another approach that will give the correct answer for small samples, but users need to be made aware that the built-in function for confidence intervals in Numbers is not what they should use in such a case.

An indication that this is not well understood is that the very examples used in the documentation for the CONFIDENCE function are all small samples. The expected distribution is Student's t, not the normal distribution. Yet the documentation also clearly states

+The confidence estimate assumes that values in the sample are normally distributed.+

Another point worth mentioning is that confidence intervals computed using t appear to be conservative at moderately large to large sample sizes (i.e., n > 60 or so). That is, they are marginally wider than the same intervals calculated using a coefficient derived from the normal distribution. This means that you are (marginally) less likely to erroneously detect differences among sample means when in fact there are none. In many applications, this is a good thing.

And finally, like other statistics confidence limits are estimators calculated from slightly to very squishy data to begin with. Close is usually good enough. If your work depends on detecting a difference just beyond the confidence limit, you really should go out and collect more and better data, just to make sure. But you can save yourself a lot of grief by using the theoretically most accurate algorithm to determine the confidence intervals in the first place.

F. Shippey & Yvan Koenig,

Thanks, yes. Your link and the User Guide give the Numbers CONFIDENCE example data that I mention in my posts, although I got the data from the Function Browser built in to Numbers.

Try calculating the confidence interval from the expression t*(s/SQRTn) using the data at that link. You can obtain the t you need from standard statistical tables for Student's t, or from the TINV function built in to Numbers. Enter the tables with the alpha value and n-1 degrees of freedom. You should get the values I calculated in my second post.

By the way, the above formula for confidence intervals is not mine. The formula t*(s/SQRTn), or some algebraic equivalent, is given in all basic statistics texts that I know of as the formula for determining confidence limits for small sample sizes from populations whose underlying distribution is expected to be normal. One example is

Zar, J. H. 1974. Biostatistical analysis. Prentice-Hall, Inc. Englewood, NJ. xiv+620 p. (There are much more recent editions.)

Also, there are many online Google-able treatments of confidence limits for the means of normally-distributed populations.

Dave Mayhood

Badunit wrote:
Assuming the Numbers function is the same as the Excel function,

http://support.microsoft.com/kb/828124

the formula is the same as =NORMSINV(1 – alpha/2) * sigma / SQRT(n)

Badunit, your link allowed me to resolve the last lingering issue. Thank you so much.

CONFIDENCE in Numbers returns the same values as CONFIDENCE in Excel as used on the example data in the above link. Clearly both spreadsheets use the CONFIDENCE function in the same way, and both call the NORMSINV function, not TINV. That is, neither uses Student's t.

The consequence is that CONFIDENCE in both Excel and Numbers will give strictly accurate results only for large samples. For small samples, it is better to use t*(s/SQRT n).

The example data in the above link show how large the difference is in a relatively large sample (n = 50). For those data

CONFIDENCE gives an upper and lower limit for the true mean as 100.8 - 109.2;
*t(s/SQRT n)* gives an upper and lower limit for the true mean as 100.7 - 109.3.

This difference is negligible for all practical purposes (even though the error is 2.5%), but remember that this is a largish sample with a fairly small standard deviation relative to the mean.

Let's say in the same example that the sample size is much smaller, which would typically lead to a much larger standard deviation. Let's use the following.

alpha = 0.05 (i.e., the same as in the example)
std deviation = 30
n = 15
mean = 105 (i.e., the same as in the example)

In this case,

CONFIDENCE returns 15.1818157426, giving lower & upper limits 89.8 - 120.2
*t(s/SQRT n)* returns 16.613446246946, giving lower & upper limits 88.4 - 121.6

The error is 8.6%. This is a problem now, because CONFIDENCE gives a clearly and falsely narrower confidence interval that could lead to erroneous conclusions.

The conclusion is that CONFIDENCE should only be used for large samples. t*(s/SQRT n) is much preferable for small samples, and I would argue as before that t*(s/SQRT n) is preferable to CONFIDENCE in all cases because it is never going to give an erroneous result -- particularly a result that is too low, which is the critical issue.

I want to thank everyone for helping me to work through this problem. It really was driving me crazy.

Dave Mayhood