Documentation/Calc Functions/B

    From The Document Foundation Wiki
    This page contains changes which are not marked for translation.

    Function name:

    B

    Category:

    Statistical Analysis

    Summary:

    Uses the probability mass function of the binomial distribution to calculate the probability of a specific number of successful trial outcomes, or a range of successful trial outcomes. The binomial distribution is a discrete probability distribution that is used to analyze data in many domains.

    Syntax:

    B(Trials; SP; T 1 [; T 2])

    Returns:

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

    Arguments:

    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%).

    T 1 is a non-negative integer, or a reference to a cell containing that integer, that specifies the lower limit for the number of successful trials.

    T 2 is a non-negative integer, or a reference to a cell containing that integer, that specifies the upper limit for the number of successful trials. If T 2 is omitted, the function calculates the probability that the number of successful trials shall be exactly T 1. If T 2 is provided, the function calculates the probability that the number of successful trials shall lie between T 1 and T 2 inclusive.

    • If any of Trials, SP, T 1, and T 2 is non-numeric, then B reports a #VALUE! error.
    • If any of Trials, T 1, and T 2 is a non-integer value, then B truncates it to an integer value.
    • If SP is less than 0.0 or greater than 1.0, then B reports an invalid argument error (Err:502).
    • For the case when T 2 is omitted, B checks (after any truncation) that Trials ≥ 0, T 1 ≥ 0, and TrialsT 1. If any of these checks fail, then B reports an invalid argument error (Err:502).
    • For the case when T 2 is provided, B checks (after any truncation) that T 1 ≥ 0, T 2T 1, and TrialsT 2. If any of these checks fail, then B reports an invalid argument error (Err:502).

    Additional details:

    • ODF 1.2 does not specify a requirement for this function. However Calc's B function performs the same calculation as the BINOM.DIST.RANGE function specified in Section 6.18.9 of ODF 1.2 and provided by other spreadsheet applications.
    • 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 B is:
    [math]\displaystyle{ \text{B}(n;\:p;\:k1;\:k2)~=~\sum_{i=k1}^{k2}\frac{n!}{i!(n-i)!}\:\times\:p^{i}\:\times\:(1-p)^{n-i} }[/math]
    If the final argument (T 2) is omitted, then set k2 to the value of k1 in this equation, effectively removing the summation operation.
    • The [math]\displaystyle{ \frac{n!}{i!(n-i)!} }[/math] term in the equation is often written as [math]\displaystyle{ \binom{n}{i} }[/math] and referred to as "n choose i" or the binomial coefficient.
    • For valid input arguments n, p, x, and y, the following relations arise:
    • [math]\displaystyle{ \text{B}(n;\:p;\:x;\:y) = \text{BINOMDIST}(y;\:n;\:p;\:TRUE) - \text{BINOMDIST}(x-1;\:n;\:p;\:TRUE) }[/math].
    • [math]\displaystyle{ \text{B}(n;\:p;\:0;\:y) = \text{BINOMDIST}(y;\:n;\:p;\:TRUE) }[/math].
    • [math]\displaystyle{ \text{B}(n;\:p;\:x) = \text{BINOMDIST}(x;\:n;\:p;\:FALSE) }[/math].
    The first of these is only valid when x > 0. For all three relations, BINOMDIST could be replaced with calls to the functionally equivalent BINOM.DIST.

    Examples:

    Formula Description Returns
    =B(A1; A2; A3) where cell A1 contains the number 10, cell A2 contains the formula =1/6, and cell A3 contains the number 2. 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. 0.290710049201722
    =B(10; 1/6; 3) 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. 0.155045359574252
    =B(10; 1/6; 2; 3) 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. Note that the formula =B(10; 1/6; 2)+B(10; 1/6; 3) performs exactly the same calculation. 0.445755408775974
    =B(10; 1/6; 0; 3) 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. Note that the formula =B(10; 1/6; 0)+B(10; 1/6; 1)+B(10; 1/6; 2)+B(10; 1/6; 3) performs exactly the same calculation. The number returned in this example is a value from the binomial cumulative distribution function (see also BINOMDIST and BINOM.DIST). 0.930272157445512
    =B(15; 50%; 7) 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. Note that the formula =B(15; 50%; 8) returns the same result because the binomial distribution is symmetrical when the probability of success is equal to 0.5. 0.196380615234375
    =B(20; 25%; 8; 20) 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. 0.101811856922723

    Related LibreOffice functions:

    BINOM.DIST

    BINOMDIST

    BINOM.INV

    CRITBINOM

    ODF standard:

    None

    Related (or similar) Excel functions:

    BINOM.DIST.RANGE since v.2013.