How to Use the BETA.DIST and BETA.INV Functions in Google Sheets for Beta Probability Distributions

When modelling probabilities, failure rates, customer conversion percentages, or project completion durations in Program Evaluation and Review Technique (PERT) analysis, variables are inherently constrained between a fixed minimum and maximum bound (such as 0% to 100%). While normal and exponential distributions extend infinitely, the Beta distribution excels at modelling continuous random variables constrained within defined finite intervals. Google Sheets provides the BETA.DIST and BETA.INV statistical formulas to evaluate cumulative probabilities, compute probability densities, and calculate inverse percentiles effortlessly.

Understanding the Beta Distribution in Analytics

The Beta distribution is characterized by two positive shape parameters: alpha ($alpha$) and beta ($eta$). These parameters dictate the skewness and kurtosis of the distribution curve. In Bayesian statistical inference, the Beta distribution serves as the conjugate prior for binomial distributions, allowing analysts to update success probabilities as new observational data arrives.

Syntax and Parameters

1. BETA.DIST Function

Calculates the cumulative Beta distribution function or probability density function.

=BETA.DIST(value, alpha, beta, cumulative, [lower_bound], [upper_bound])

• value (Required): The value between lower_bound and upper_bound at which to evaluate the function.
• alpha (Required): The first shape parameter ($alpha > 0$).
• beta (Required): The second shape parameter ($eta > 0$).
• cumulative (Required): TRUE for cumulative distribution; FALSE for probability density.
• lower_bound (Optional): Lower bound of the interval (default is 0).
• upper_bound (Optional): Upper bound of the interval (default is 1).

2. BETA.INV Function

Calculates the inverse of the cumulative Beta distribution function.

=BETA.INV(probability, alpha, beta, [lower_bound], [upper_bound])

• probability (Required): A probability value between 0 and 1 ($0 < p < 1$).
• alpha (Required): The shape parameter ($alpha > 0$).
• beta (Required): The shape parameter ($eta > 0$).
• lower_bound (Optional): Lower interval bound (default is 0).
• upper_bound (Optional): Upper interval bound (default is 1).

Step-by-Step Practical Implementation

Step 1: Calculating Cumulative Probability with BETA.DIST

1. Open Google Sheets and enter test value 0.65 in cell A2.
2. Enter alpha parameter 8 in cell B2 and beta parameter 4 in cell C2.
3. In cell D2, compute the cumulative probability of achieving at most a 65% success rate:

   =BETA.DIST(A2, B2, C2, TRUE)

4. Press Enter. Google Sheets outputs 0.4086 (40.86% cumulative probability).

Step 2: Modelling Bounded Project Schedules (PERT Analysis)

If a project task takes between 5 days (optimistic lower bound) and 25 days (pessimistic upper bound) with parameters $alpha = 3$ and $eta = 5$:

1. In cell E2, calculate the probability that the task finishes in 14 days or less:

   =BETA.DIST(14, 3, 5, TRUE, 5, 25)

2. Press Enter. Google Sheets returns 0.5843 (58.43% probability of completion within 14 days).

Step 3: Calculating Critical Percentile Thresholds with BETA.INV

1. To find the 95th percentile project completion timeline:
2. In cell F2, enter:

   =BETA.INV(0.95, 3, 5, 5, 25)

3. Press Enter. The formula outputs 18.28 days, providing an accurate buffer target.

Comparison of Continuous Distribution Formulas in Google Sheets

Distribution Function	Domain Interval	Primary Shape Parameters	Key Analytical Strength	Primary Use Case
BETA.DIST	Bounded $[A, B]$ (Typically $[0, 1]$)	$alpha > 0$, $eta > 0$	Flexible skewness over finite bounds	Conversion rates, click-through rates, PERT duration risk
NORM.DIST	Unbounded $(-infty, infty)$	$mu$ (Mean), $sigma$ (StdDev)	Symmetric bell curve distribution	Manufacturing tolerances and physical measurement variations
GAMMA.DIST	Semi-bounded $[0, infty)$	$alpha$ (Shape), $eta$ (Scale)	Right-skewed positive waiting times	Queuing theory and rainfall precipitation modelling
WEIBULL	Semi-bounded $[0, infty)$	$k$ (Shape), $lambda$ (Scale)	Variable hazard rates over time	Industrial equipment lifecycle and component failure analysis

Troubleshooting Common Errors

• #NUM! Error in Shape Parameters: Occurs when $alpha le 0$ or $eta le 0$. Both shape parameters must be strictly positive.
• Value Outside Bounds: If value < lower_bound or value > upper_bound, BETA.DIST returns #NUM!.
• Invalid Bounds: If lower_bound $ge$ upper_bound, both functions return #NUM!.