Probability and Statistics in Microsoft Excel™

Print this page

Confidence Limits

Assuming degree of confidence 100(1-$$\alpha$$)% (e.g. for 95% confidence $$\alpha$$ =0.05):

One-sample statistics, with data in range x

For $$\mu$$ ($$\sigma$$ known)	Lower limit	=AVERAGE(x)-NORMSINV(1-alpha/2)*sigma/SQRT(COUNT(x))
	or	=AVERAGE(x)-CONFIDENCE(alpha, sigma, COUNT(x))
	Upper limit	=AVERAGE(x)+NORMSINV(1-alpha/2)*sigma/SQRT(COUNT(x))
	or	=AVERAGE(x)+CONFIDENCE(alpha, sigma, COUNT(x))
For $$\mu$$ ($$\sigma$$ unknown)	Lower limit	=AVERAGE(x)-TINV(alpha, COUNT(x)-1)*STDEV(x)/SQRT(COUNT(x))
	Upper limit	=AVERAGE(x)+TINV(alpha, COUNT(x)-1)*STDEV(x)/SQRT(COUNT(x))
For $$\sigma^2$$	Lower limit	=(DEVSQ(x)/CHIINV(alpha/2,COUNT(x))-1)
	Upper limit	=(DEVSQ(x)/CHIINV(1-alpha/2,COUNT(x))-1)

Two-sample statistics, with data for the first sample in range x, and the second sample in range y

For $$\mu_x - \mu_y$$ ($$\sigma_x$$ known, $$\sigma_y$$ known).

Lower limit

=AVERAGE(x)-AVERAGE(y)-NORMSINV(1-alpha/2)*SQRT(sigmax^2/COUNT(x)+ sigmay^2/COUNT(y))

Upper limit

=AVERAGE(x)-AVERAGE(y)+NORMSINV(1-alpha/2)* SQRT(sigmax^2/COUNT(x)+ sigmay^2/COUNT(y))

For $$\mu_x - \mu_y$$ ($$\sigma_x$$ and $$\sigma_y$$ unknown but assumed equal)

Estimate the unknown common standard deviation by the pooled estimate:

s	=SQRT((DEVSQ(x)+DEVSQ(y))/( COUNT(x)+COUNT(y)-2))

Lower limit

=AVERAGE(x)-AVERAGE(y)-TINV(alpha,COUNT(x)+COUNT(y)-2)*s*SQRT(1/COUNT(x)+ 1/COUNT(y))

Upper limit

=AVERAGE(x)-AVERAGE(y)+TINV(alpha,COUNT(x)+COUNT(y)-2)* s*SQRT(1/COUNT(x)+ 1/COUNT(y))

For $$\sigma_x^2 / \sigma_y^2$$

Lower limit	=DEVSQ(x)/DEVSQ(y)/FINV(alpha/2, COUNT(x)-1, COUNT(y)-1)
Upper limit	=(DEVSQ(x)/DEVSQ(y)/FINV(1-alpha/2, COUNT(x)-1, COUNT(y)-1)

contents

close this window