BETA.DIST in Google Sheets

What is BETA.DIST in Google Sheets?

The BETA.DIST function in Google Sheets is used to calculate the beta probability distribution for a specified value of x, given values for alpha, beta, and optional bounds. It helps determine the probability associated with a specific x value within a beta distribution—particularly useful when analyzing percentages, probabilities, or rates. Depending on your choice of TRUE or FALSE for the cumulative argument, the function returns either the cumulative distribution (CDF) or the probability density (PDF).

Suppose we must find the cumulative probability that a conversion rate is less than or equal to 0.6 (60%) under a beta distribution. Here, α = 3 and β = 5. Since α is less than β, the distribution is skewed to the right. In this case, using the BETA.DIST function with the cumulative option returns the probability that the true value of x is 60% or less. Enter the formula

=BETA.DIST(0.6, 3, 5, TRUE) to get the result. Here, we get 0.9037 or 90.37% the x value is less than 0.6.

Syntax

The syntax of BETA.DIST is as follows:

=BETA.DIST(x, alpha, beta, cumulative, [A], [B])

Arguments

1. x – The value at which to evaluate the distribution. This is the specific point you’re calculating the probability for.
2. alpha – A shape parameter of the distribution. This must be a positive number.
3. beta – Another shape parameter of the distribution. It must be greater than zero.
4. cumulative – A logical value (TRUE or FALSE). Use TRUE if you want the cumulative distribution function (CDF), which gives the probability that a value is less than or equal to x. Use FALSE for the probability density function (PDF), which returns the height of the curve at x.
5. A (optional) – The lower bound of the distribution’s interval. The default is 0.
6. B (optional) – The upper bound of the distribution’s interval. If omitted, defaults to 1.

How To Use BETA.DIST Function in Google Sheets?

Now that we have a brief introduction and learned the syntax of BETA.DIST, let us look at how to use it in Google Sheets. To use BETA.DIST in Google Sheets, there are two ways.

1. One is to enter BETA.DIST manually
2. The other way is to enter it through the menu bar.

Enter BETA.DIST Manually

Let us look at how to calculate the cumulative beta distribution using BETA.DIST entered manually.

Step 1: Suppose we want to find the cumulative probability at x = 0.5 for a beta distribution with alpha = 2 and beta = 3. Enter the following values in a sheet.

Step 2: In any empty cell in Google Sheets, enter the formula:

=BETA.DIST(B1,B2,B3,TRUE). Alternatively, you can also enter the direct values as follows.

Type the function name and open the parentheses. Separate the arguments with a comma and close the parenthesis.

=BETA.DIST(0.5, 2, 3, TRUE)

Step 3: Press Enter. The result will show the probability that a value from this beta distribution is less than or equal to 0.5.

This way, we can see how to use BETA DIST in Google Sheets manually with a simple and practical example.

Entering BETA.DIST Through the Menu Bar

1. Go to the Insert tab in Google Sheets.
2. Select the option Function and then Statistical.
3. From the list of functions, select the BETA.DIST function and enter the arguments one by one.
4. Close the parentheses and press Enter to get the result.

Examples

By understanding how to shape the beta distribution using alpha and beta, and properly interpreting the output, you can add a powerful layer of statistical insight to your spreadsheets. Let us look at some examples of how to do the same.

Example #1 – Estimate the Success Rate of a New ad Based on Historical Data

In this example, a marketing team recently launched a new advertisement campaign. Earlier, similar ad campaigns resulted in about 70 conversions out of 800 views. The team wants to estimate the success rate of the new ad. Let us find the 90% confidence interval and compare how the current campaign performs compared to past trends.

Step 1: We know that there were 70 conversions out of 800 views. We can calculate the number of failures as 800 – 70 = 730.

Here, the alpha value is 70 and the beta value is 730. Let us enter these values in a new Google Sheet.

Step 2: Let us calculate the upper and lower bounds at a 90% confidence interval. 

This is how the lower bound is calculated.

The probability value is (1 – 0.90) / 2 = 0.05.

Now, we enter the following formula in cell B4:

=BETA.INV(0.05, 70, 730)

Press Enter to get the lower bound.

Step 3: Now, let us proceed to find the upper bound. The corresponding probability is 1 – 0.05 = 0.95. In cell B5, we enter:

=BETA.INV(0.95, 70, 730)

You get the result for the upper bound when you press Enter.

The result shows us that the lower and upper bounds come out to be approximately 7.16% and 10.44%. This indicates that the team can be 90% confident that the success rate of other ad campaigns lies between these two values.

Example #2 – Evaluate a Player’s Performance Metrics

Consider a football player who has attempted 120 shots on goal. He has successfully scored 28 times during that particular season. His coach wants to evaluate his scoring rate. He wishes to calculate a 95% confidence interval thereby understanding the performance of the player.

Step 1: The player has 28 goals. Let us calculate the number of misses as 120 – 28 = 92. From this, we get values of alpha = 28 and beta = 92. Enter these values into a Google Sheet.

Step 2: Let us calculate the lower bound of the 95% confidence interval.

Find the probability value at:

(1 – 0.95) / 2 = 0.025.

Now enter the following formula in cell B3:

=BETA.INV(0.025, B1, B2)

Press Enter to get the lower bound.

Step 3: The probability value for the upper bound: 1 – 0.025 = 0.975.

In cell B3, enter the formula:

=BETA.INV(0.975, B1, B2)

Press Enter to see the upper bound.

The output gives a confidence interval between 16.2% to 31.2%. It shows that the coach can be 95% confident about the player’s true scoring rate which is between 17.8% and 30.2%.

Example #3 – Assess the Probability of Defects in a Production Process

A factory produces 1500 items in a month. Out of these, 45 items were found to be defective. The QC team wishes to assess the probability of defects at a 95% confidence interval and evaluate the reliability of the production process.

Step 1: As mentioned, there are 45 defective items. Hence, the number of non-defective items is 1500 – 45 = 1455. Hence, alpha = 45 and beta = 1455. Enter these values into a sheet.

Step 2: Lower bound of the 95% confidence interval

The probability is: (1 – 0.95) / 2 = 0.025.

Enter the following formula to get the lower bound.

=BETA.INV(0.025, 45, 1455)

Press Enter.

Step 3: Here, the upper bound’s probability is 0.975.

Enter the following formula in cell B4 for the upper bound.

=BETA.INV(0.975, 45, 1455).

Press Enter.

We get the results between 2.19% to 3.92%. It means there is a 95% chance that the defect rate lies between these two percentages.

Important Things To Note

1. The BETA.DIST function returns the cumulative probability or the probability density based on whether the cumulative argument is TRUE or FALSE.
2. The x-value must always lie between the lower and upper bound. If A and B are not specified, it should be between 0 and 1.
3. BETA.DIST is commonly used in reliability analysis, project forecasting, and modeling uncertainty, especially when dealing with proportions or percentages.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand BETA.DIST in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is BETA.DIST in Google Sheets. We learn how to use it to find the beta probability distribution for a given x value with examples. You can learn more from the following articles. –

Break-Even Analysis in Google Sheets

MODE.MULT in Google Sheets