Updated: 31 July 2015
Use CONFIDENCE_NORM to calculate the confidence interval for a population mean, using a normal distribution. CONFIDENCE_NORM is identical to the CONFIDENCE function and has been added to XLeratorDB simply to maintain compatibility with Excel 2010 and subsequent Excel releases.
The significance level used to calculate the confidence interval. @Alpha must be of a type float or of type that intrinsically converts to float.
The population standard deviation. @Standard_dev must be of a type float or of a type that intrinsically converts to float.
The sample size. @Size must be of a type float or of a type the intrinsically converts to float.
· 0 < @Alpha < 1
· 0 < Standard_dev
· 0 < @Size
Given a population mean of 100 and a population standard deviation of 100, calculate the 95% confidence interval for a sample size of 600.
wct.CONFIDENCE_NORM(0.05,15,600) as CI
This produces the following result
The CONFIDENCE_NORM function is closely related to the NORMSINV function.
wct.CONFIDENCE_NORM(alpha,mu,sigma) as CI
,wct.NORMSINV(1-alpha*0.5)*mu/SQRT(sigma) as z
This produces the following result.
The confidence interval does not return the width of the entire interval, just the width of the upper half, which is identical in the standard normal distribution to the width of the lower half.
Using the data from the above examples, we will generate random numbers from a distribution with a mean of 100 and a standard deviation of 15. We will simulate 100 trials from which we would expect, on average, about 5 (because we are using the 95% confidence interval) to generate a sample mean either below or above the population mean ± the confidence interval.
,AVG(x) as mean
,STDEV(x) as sigma
,COUNT(x) as size
k.seq % 100 as Trial
mean > 100+wct.CONFIDENCE_NORM(0.05,15,600)
OR MEAN < 100-wct.CONFIDENCE_NORM(0.05,15,600)
This produces the following result. Your results will be different.