BINOMDIST Function in Google Sheets 💻 | 2022 Guide (+ Examples)

BINOMDIST Function in Google Sheets 💻 | 2022 Guide (+ Examples)
Reading Time: 4 minutes

How to use the Binomdist function in Google Sheets

Understand how to use the BINOMDIST function in Google Sheets with our definitive guide containing a step-by-step formula breakdown with examples!

What is a Binomial Distribution?

The word Binomial is used to refer to any event which has two possible outcomes- success or failure. For instance, the result of whether someone will get selected in an interview can either be a yes or no. The probability distribution of any such event, experiment, or survey which has possible outcomes of success or failure is known as Binomial Distribution.

You may want to understand Binomial Distributions first and if you’re already familiar, continue reading below to understand how to use Binomdist function in Google Sheets.

The Binomdist formula in Google Sheets helps to calculate the probability of getting a certain number of successes of an outcome repeated over a number of trials when the probability of success for a single trial is known.

If the theory sounds too complicated, let us demystify it with a simple example! Consider a toss with a biased coin the probability for heads showing up is 60%. The coin was thrown 10 times and heads appeared 6 times. Then the Binomdist function in Google Sheets would help compute the probability of the event (Heads shows 6 times out of 10) given the individual probability of a single coin toss ie 0.6.

The binomdist function in Google Sheets thus takes three input parameters –

  1. Number of trials (N)
  2. Number of successful attempts (X)
  3. Probability of a single successful attempt (P)

Before Using Binomdist function in Google Sheets

Be mindful of the following rule

✅ A fixed number of trials are provided

✅ Binary outcome – only two outcomes are possible (positive or negative)

✅ Independency – Each trial’s outcome should be independent of another trial’s outcome

Syntax

=BINOMDIST(num_successes, num_trials, prob_success, cumulative)

  1. num_successes– number of successful trials of an event
  2. num_trials– total number of trials of an event
  3. prob_success– the probability of success
  4. cumulative– takes boolean values of true or false. The value is true when the cumulative sum of the probabilities needs to be calculated. The value is false when the probability mass function is to be calculated. Takes false as default value.

Data

In this article, I have considered the experiment of a coin tossed 10 times, with heads showing 6 times and the probability of a heads being 0.6. The occurrence of the head is considered to be the successful outcome of this event. The data shown below tabulates the results of this experiment. 

Data showing the results of an experiment of rolling a die 10 times | Used for Binomdist function in Google Sheets
Fig 1: Data

Example 1: Calculating the Probability of an outcome

  • In this case, we calculate the probability of occurrence of a particular outcome. Here cumulative = ‘false’.
  • Select a cell. 
  • Start typing ‘=’ followed by the name of the function ‘BINOMDIST’ in the cell. As you type, Google Sheets will automatically suggest the required function. Choose this function. 
  • To calculate the probability of occurrence of an event use the syntax:

=BINOMDIST(num_successes, num_trials, prob_success, false)

  • Assign the appropriate values to all the other variables. 
Using the BINOMDIST function in Google Sheets to calculate the probability of a successful outcome
Fig 2: Using the BINOMDIST function in Google Sheets to calculate the probability of a successful outcome
  • In a similar manner, we can calculate the probabilities for all the other values of the number of successes. 
Table showing the probabilities for different number of successful outcomes | Used for Binomdist function in Google Sheets
Fig 3: Probabilities for different number of successful outcomes using Binomdist function in Google Sheets

Example 2: Calculating the Cumulative Probability

  • Cumulative Sum of Probabilities up to the occurrence of a certain event gives the sum of all the probabilities of all the events occurring before that event. In this case, cumulative = ‘true’.
  • Select cell. 
  • To generate the cumulative sum of probabilities use the syntax:

=BINOMDIST(num_successes, num_trials, prob_success, true)

  • Assign the appropriate values to all the other variables. 
Using the BINOMDIST function in Google Sheets to calculate Cumulative Probabilities 
Fig 4: Using the BINOMDIST function in Google Sheets to calculate Cumulative Probabilities 
  • In a similar manner, we can calculate the cumulative probabilities for all the other values of the number of successes. 
Resultant table showing Cumulative Probabilities for different number of successful outcomes
Fig 5: Cumulative Probabilities for different number of successful outcomes

Distribution Table

Finally, after the respective Probabilities and Cumulative Probabilities have been calculated, we can create a Distribution Table that shows the final results in a well-organized tabulated manner. 

Distribution Table in Google Sheets
Fig 6: Final Distribution Table

See Also