Documentation/Calc Functions/BINOM.DIST

    From The Document Foundation Wiki

    Function name:

    BINOM.DIST

    Category:

    Statistical Analysis

    Summary:

    Calculates binomial distribution probabilities from either the probability mass function or the cumulative distribution function. The binomial distribution is a discrete probability distribution that is used to analyze data in many domains.

    Syntax:

    BINOM.DIST(X; Trials; SP; C)

    Returns:

    Returns a real number in the range [0, 1], which is the binomial distribution probability for the given arguments.

    Arguments:

    X is a non-negative integer, or a reference to a cell containing that integer, that is the number of trial successes for which the probability is required.

    Trials is a non-negative integer, or a reference to a cell containing that integer, that is the total number of independent trials.

    SP is a real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, that is the probability of a successful outcome on each trial. As a probability, SP lies in the range [0, 1] (or equivalently 0% ≤ SP ≤ 100%).

    C is a logical value, or a reference to a cell containing that value, that determines whether the required probability is taken from the probability mass function or the cumulative distribution function. If C is set to 0 or FALSE, a value from the probability mass function is calculated. For any other values of C, a value from the cumulative distribution function is calculated.

    • If any of X, Trials, SP, and C is non-numeric, then BINOM.DIST reports a #VALUE! error.
    • If either of X or Trials is a non-integer value, then BINOM.DIST truncates it to an integer value.
    • If SP is less than 0.0 or greater than 1.0, then BINOM.DIST reports an invalid argument error (Err:502).
    • BINOM.DIST checks (after any truncation) that Trials ≥ 0, X ≥ 0, and TrialsX. If any of these checks fail, then BINOM.DIST reports an invalid argument error (Err:502).

    Additional details:

    • Calc's BINOM.DIST and BINOMDIST functions perform the same calculations. The requirements for BINOMDIST are specified in ODF 1.2; BINOM.DIST is provided for interoperability with Microsoft Excel.
    • The name space for BINOM.DIST is COM.MICROSOFT.BINOM.DIST.
    • The binomial distribution should be used in its intended circumstances and this is when there are a fixed number of independent trials, each having two possible outcomes (success or failure), with a constant probability of achieving a successful outcome for each trial.
    • The formula for BINOM.DIST when C is set to 0 or FALSE (probability mass function) is:
    [math]\displaystyle{ \text{BINOM.DIST}(x;\:n;\:p;\:0)~=~\frac{n!}{x!(n-x)!}\:\times\:p^{x}\:\times\:(1-p)^{n-x} }[/math]
    The [math]\displaystyle{ \frac{n!}{x!(n-x)!} }[/math] term in the above equation is often written as [math]\displaystyle{ \binom{n}{x} }[/math] and referred to as "n choose x" or the binomial coefficient.
    • The formula for BINOM.DIST when C is set to any value other than 0 and FALSE (cumulative density function) is:
    [math]\displaystyle{ \text{BINOM.DIST}(x;\:n;\:p;\:TRUE)~=~\sum_{j=0}^{x} \frac{n!}{j!(n-j)!}\:\times\:p^{j}\:\times\:(1-p)^{n-j} }[/math]
    • For valid input arguments c, n, p, x, and y, the following relations arise:
    • [math]\displaystyle{ \text{BINOMDIST}(x;\:n;\:p;\:c)~=~\text{BINOM.DIST}(x;\:n;\:p;\:c) }[/math]
    • [math]\displaystyle{ \text{B}(n;\:p;\:x;\:y)~=~\text{BINOM.DIST}(y;\:n;\:p;\:TRUE) - \text{BINOM.DIST}(x-1;\:n;\:p;\:TRUE) }[/math]
    • [math]\displaystyle{ \text{B}(n;\:p;\:0;\:y)~=~\text{BINOM.DIST}(y;\:n;\:p;\:TRUE) }[/math]
    • [math]\displaystyle{ \text{B}(n;\:p;\:x) = \text{BINOM.DIST}(x;\:n;\:p;\:FALSE) }[/math].
    The second of these is only valid when x > 0. For the final three relations, BINOM.DIST could be replaced with calls to the functionally equivalent BINOMDIST.

    Examples:

    Formula Description Returns
    =BINOM.DIST(A1; A2; A3; A4) where cell A1 contains the number 2, cell A2 contains the number 10, cell A3 contains the formula =1/6, and cell A4 contains the formula =FALSE(). Suppose that we roll a fair (unbiased) die 10 times. For each roll, the result will be either 1, 2, 3, 4, 5, or 6, and each of these will occur with an equal probability of 1/6. Here the function calculates the probability that a specific number (it doesn't matter which) will come up exactly twice in the 10 rolls, using the probability mass function. 0.290710049201722
    =BINOM.DIST(3; 10; 1/6; 0) Assuming the same scenario as in the previous example, here the function calculates the probability that a specific number (it doesn't matter which) will come up exactly three times in the 10 rolls, using the probability mass function. 0.155045359574252
    =BINOM.DIST(2; 10; 1/6; 0) + BINOM.DIST(3; 10; 1/6; 0) Assuming the same scenario as in the previous examples, here the function calculates the probability that a specific number (it doesn't matter which) will come up exactly two or exactly three times in the 10 rolls, using the probability mass function. Note that using the cumulative distribution function, the formula =BINOM.DIST(3; 10; 1/6; 1) - BINOM.DIST(1; 10; 1/6; 1) performs exactly the same calculation. 0.445755408775974
    =BINOM.DIST(3; 10; 1/6; 1) Assuming the same scenario as in the previous examples, here the function calculates the probability that a specific number (it doesn't matter which) will come up exactly zero times, or exactly one time, or exactly two times, or exactly three times in the 10 rolls. The number returned in this example is a value from the cumulative distribution function. Note that the formula =BINOM.DIST(0; 10; 1/6; 0) + BINOM.DIST(1; 10; 1/6; 0) + BINOM.DIST(2; 10; 1/6; 0) + BINOM.DIST(3; 10; 1/6; 0) performs exactly the same calculation. 0.930272157445512
    =BINOM.DIST(7; 15; 50%; 0) Suppose that we toss a fair (unbiased) coin 15 times. For each toss, the result will be either a head or a tail, and each of these will occur with an equal probability of 0.5 or 50%. Here the function calculates the probability that heads will come up exactly seven times in the 15 tosses, using the probability mass function. Note that the formula =BINOM.DIST(8; 15; 50%; 0) returns the same result because the binomial distribution is symmetrical when the probability of success is equal to 0.5. 0.196380615234375
    =1 - BINOM.DIST(7, 20, 25%, TRUE()) Suppose that we are to sit an important Quantum Cosmology examination but, unfortunately, we have not attended any lectures and know nothing at all about the subject. We discover that the exam comprises 20 multiple-choice questions, each with four possible options. Our strategy will be to randomly guess the answer to each question from these four options. Here the function calculates the probability that we will achieve a mark of a least 40% using this strategy, using the cumulative distribution function. 0.101811856922723

    Related LibreOffice functions:

    B

    BINOMDIST

    BINOM.INV

    CRITBINOM

    ODF standard:

    None

    Related (or similar) Excel functions:

    BINOM.DIST