Documentation/Calc Functions/CONFIDENCE

    From The Document Foundation Wiki

    Function name:

    CONFIDENCE

    Category:

    Statistical Analysis

    Summary:

    Calculates half the width of the confidence interval for a population mean, using the normal distribution. The confidence interval is a range of values that is expected to encompass the true population mean, with a specified degree of certainty.

    Half the width of the confidence interval is often known as the margin of error and then the confidence interval is equal to the sample mean ± the margin of error.

    Use CONFIDENCE when you know the population standard deviation in advance.

    Syntax:

    CONFIDENCE(Alpha; STDEV; Size)

    Returns:

    Returns a positive real number, which is half the width of the confidence interval for the population mean given the specified arguments.

    Arguments:

    Alpha is a real number in the range 0 < Alpha < 1, or a reference to a cell containing that number, which is the likelihood that the population mean lies outside the confidence interval. The confidence level is the likelihood that the population mean lies inside the confidence interval and is equal to 1-Alpha.

    STDEV is a positive real number, or a reference to a cell containing that number, which is the standard deviation for the total population.

    Size is a positive integer, or a reference to a cell containing that number, which is the sample size.

    • If any of Alpha, STDEV, or Size is non-numeric, then CONFIDENCE reports a #VALUE! error.
    • If Alpha is less than or equal to 0.0, or greater than or equal to 1.0, then CONFIDENCE reports an invalid argument error (Err:502).
    • If STDEV is less than or equal to 0.0, then CONFIDENCE reports an invalid argument error (Err:502).
    • If Size is not an integer value, then CONFIDENCE truncates it to an integer value.
    • If Size is less than 1, then CONFIDENCE reports an invalid argument error (Err:502).

    Additional details:

    • Calc's CONFIDENCE and CONFIDENCE.NORM functions perform identical calculations. The requirements for CONFIDENCE are specified in ODF 1.2; CONFIDENCE.NORM is provided for interoperability with Microsoft Excel.
    • The equation for CONFIDENCE is:
    [math]\displaystyle{ \text{CONFIDENCE}(\alpha;\:\sigma;\:n)~=~z\:\times\:\frac{\sigma}{\sqrt{n}} }[/math]
    where z is the z-score for the given confidence level (assuming two tails). For example, a confidence level of 90% gives a z-score of 1.645; a confidence level of 95% gives a z-score of 1.960; a confidence level of 99% gives a z-score of 2.576.
    The confidence interval (CI) is then given by:
    [math]\displaystyle{ CI~=~\bar{x}~\pm \text{CONFIDENCE}(\alpha;\:\sigma;\:n) }[/math]
    where [math]\displaystyle{ \bar{x} }[/math] is the sample mean.
    • For in-range arguments, the formula =NORMINV(1 - Alpha / 2; 0; 1) * STDEV / SQRT (Size) is equivalent to =CONFIDENCE(Alpha; STDEV; Size).

    Examples:

    Formula Description Returns
    =CONFIDENCE(A1; A2; A3) where cells A1:A3 contain the values 0.05, 2.5, and 50 respectively. Here the function calculates the margin of error for the population mean, assuming a significance level of 0.05, a population standard deviation of 2.5, and a sample size of 50. The significance level of 5% corresponds to a confidence level of 95% and a z-score of 1.96. Note that the formula =NORM.DIST(1.96, 0, 1, 1)-NORM.DIST(-1.96, 0, 1, 1) is approximately equal to 0.95. 0.692951912174839
    =CONFIDENCE(1%; 3.78; 100) Here the function calculates the margin of error for the population mean, assuming a significance level of 0.01, a population standard deviation of 3.78, and a sample size of 100. The significance level of 1% corresponds to a confidence level of 99% and a z-score of 2.576. 0.973663476741484
    =CONFIDENCE(1%; 3.78; 1000) Similar to the previous example, except for the sample size of 1000. Note how the greater number of samples narrows the confidence interval. 0.307899426102147

    Related LibreOffice functions:

    CONFIDENCE.NORM

    CONFIDENCE.T

    ODF standard:

    Section 6.18.16, part 2

    Related (or similar) Excel functions:

    CONFIDENCE