CONFIDENCE_NORM
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.
Syntax
SELECT [wct].[CONFIDENCE_NORM](
<@Alpha, float,>
,<@Standard_dev, float,>
,<@Size, float,>)
Arguments
@Alpha
The significance level used to calculate the confidence interval. @Alpha must be of a type float or of type that intrinsically converts to float.
@Standard_dev
The population standard deviation. @Standard_dev must be of a type float or of a type that intrinsically converts to float.
@Size
The sample size. @Size must be of a type float or of a type the intrinsically converts to float.
Return Type
float
Remarks
· 0 < @Alpha < 1
· 0 < Standard_dev
· 0 < @Size
Examples
Given a population mean of 100 and a population standard deviation of 100, calculate the 95% confidence interval for a sample size of 600.
SELECT
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.
SELECT
wct.CONFIDENCE_NORM(alpha,mu,sigma) as CI
,wct.NORMSINV(1-alpha*0.5)*mu/SQRT(sigma) as z
FROM (VALUES(.05,15,600))n(alpha,mu,sigma)
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.
SELECT
*
FROM (
SELECT
Trial
,AVG(x) as mean
,STDEV(x) as sigma
,COUNT(x) as size
FROM (
SELECT
k.seq % 100 as Trial
,k.x
FROM
wctMath.wct.RANDNORMAL(100*600,100,15)k
)n
GROUP BY
n.trial
)p
WHERE
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.
See Also