BETABINOM
Overview
The BETABINOM function computes values related to the Beta-binomial discrete distribution, which describes the number of successes in n Bernoulli trials where the probability of success follows a beta distribution. This function can return the probability mass function (PMF), cumulative distribution function (CDF), survival function (SF), inverse CDF (quantile/ICDF), inverse SF (ISF), mean, variance, standard deviation, or median for a given value.
Excel does not provide a native Beta-binomial distribution function. The Python function in Excel provided here supports all major distribution features, including PMF, CDF, survival function, inverse CDF (quantile), inverse survival function, and distribution statistics (mean, median, variance, standard deviation).
For more details, see the scipy.stats.betabinom documentation .
Usage
To use the function in Excel:
=BETABINOM(k, n, a, b, [mode], [loc])k(float or 2D list, required): Value(s) at which to evaluate the distribution. For PMF, CDF, SF, ICDF, and ISF, this is the integer value (0 <= k <= n).n(int, required): Number of trials (n >= 0).a(float, required): Alpha parameter of the beta distribution (a > 0).b(float, required): Beta parameter of the beta distribution (b > 0).mode(str, optional, default=“pmf”): Output type. One of"pmf","cdf","sf","icdf","isf","mean","var","std", or"median".loc(float, optional, default=0): Location parameter (shifts the distribution).
The function returns a scalar or 2D list of floats (for array input), or an error message (string) if the input is invalid. The output depends on the selected mode:
pmf: Probability mass function at k.cdf: Cumulative distribution function at k.sf: Survival function (1 - CDF) at k.icdf: Inverse CDF (quantile) for probability k.isf: Inverse survival function for probability k.mean: Mean of the distribution.var: Variance of the distribution.std: Standard deviation of the distribution.median: Median of the distribution.
Examples
Example 1: PMF at k=2, n=5, a=2.3, b=0.63
Inputs:
| k | n | a | b | mode | loc |
|---|---|---|---|---|---|
| 2 | 5 | 2.3 | 0.63 | pmf | 0 |
Excel formula:
=BETABINOM(2, 5, 2.3, 0.63, "pmf", 0)Expected output:
| Result |
|---|
| 0.0879 |
Example 2: CDF at k=2, n=5, a=2.3, b=0.63
Inputs:
| k | n | a | b | mode | loc |
|---|---|---|---|---|---|
| 2 | 5 | 2.3 | 0.63 | cdf | 0 |
Excel formula:
=BETABINOM(2, 5, 2.3, 0.63, "cdf", 0)Expected output:
| Result |
|---|
| 0.1557 |
Example 3: Survival Function at k=2, n=5, a=2.3, b=0.63
Inputs:
| k | n | a | b | mode | loc |
|---|---|---|---|---|---|
| 2 | 5 | 2.3 | 0.63 | sf | 0 |
Excel formula:
=BETABINOM(2, 5, 2.3, 0.63, "sf", 0)Expected output:
| Result |
|---|
| 0.8443 |
Example 4: Inverse CDF (ICDF) for probability k=0.5, n=5, a=2.3, b=0.63
Inputs:
| k | n | a | b | mode | loc |
|---|---|---|---|---|---|
| 0.5 | 5 | 2.3 | 0.63 | icdf | 0 |
Excel formula:
=BETABINOM(0.5, 5, 2.3, 0.63, "icdf", 0)Expected output:
| Result |
|---|
| 4 |
Example 5: Mean, Variance, Std, Median
Inputs:
| k | n | a | b | mode | loc |
|---|---|---|---|---|---|
| 0 | 5 | 2.3 | 0.63 | mean | 0 |
| 0 | 5 | 2.3 | 0.63 | var | 0 |
| 0 | 5 | 2.3 | 0.63 | std | 0 |
| 0 | 5 | 2.3 | 0.63 | median | 0 |
Excel formulas:
=BETABINOM(0, 5, 2.3, 0.63, "mean", 0)
=BETABINOM(0, 5, 2.3, 0.63, "var", 0)
=BETABINOM(0, 5, 2.3, 0.63, "std", 0)
=BETABINOM(0, 5, 2.3, 0.63, "median", 0)Expected outputs:
| Result |
|---|
| 3.9249 |
| 1.7029 |
| 1.3049 |
| 4 |
Python Code
from scipy.stats import betabinom as scipy_betabinom
def betabinom(k, n, a, b, mode="pmf", loc=0):
"""
Compute Beta-binomial distribution values: PMF, CDF, SF, ICDF, ISF, mean, variance, std, or median.
Args:
k: Value(s) at which to evaluate (float or 2D list).
n: Number of trials (int, n >= 0).
a: Alpha parameter (float, a > 0).
b: Beta parameter (float, b > 0).
mode: Output type: 'pmf', 'cdf', 'sf', 'icdf', 'isf', 'mean', 'var', 'std', or 'median'.
loc: Location parameter (float, default 0).
Returns:
Scalar or 2D list of floats, or error message (str) if invalid.
"""
# Validate n, a, b
try:
n_val = int(n)
a_val = float(a)
b_val = float(b)
if not (n_val >= 0 and a_val > 0 and b_val > 0):
return "Invalid input: n must be >= 0, a and b must be > 0."
except Exception:
return "Invalid input: n must be integer, a and b must be numbers."
# Validate loc
try:
loc_val = float(loc)
except Exception:
return "Invalid input: loc must be a number."
# Validate mode
valid_modes = ["pmf", "cdf", "sf", "icdf", "isf", "mean", "var", "std", "median"]
if not isinstance(mode, str) or mode not in valid_modes:
return f"Invalid input: mode must be one of {valid_modes}."
# Helper to process k (scalar or 2D list)
def process_k(val):
try:
return float(val)
except Exception:
return None
# Handle statistics
if mode == "mean":
return scipy_betabinom.mean(n_val, a_val, b_val, loc=loc_val)
if mode == "var":
return scipy_betabinom.var(n_val, a_val, b_val, loc=loc_val)
if mode == "std":
return scipy_betabinom.std(n_val, a_val, b_val, loc=loc_val)
if mode == "median":
return scipy_betabinom.median(n_val, a_val, b_val, loc=loc_val)
# PMF, CDF, SF, ICDF, ISF
def compute(val):
kval = process_k(val)
if kval is None:
return "Invalid input: k must be a number."
if mode == "pmf":
return float(scipy_betabinom.pmf(kval, n_val, a_val, b_val, loc=loc_val))
elif mode == "cdf":
return float(scipy_betabinom.cdf(kval, n_val, a_val, b_val, loc=loc_val))
elif mode == "sf":
return float(scipy_betabinom.sf(kval, n_val, a_val, b_val, loc=loc_val))
elif mode == "icdf":
return float(scipy_betabinom.ppf(kval, n_val, a_val, b_val, loc=loc_val))
elif mode == "isf":
return float(scipy_betabinom.isf(kval, n_val, a_val, b_val, loc=loc_val))
# 2D list or scalar
if isinstance(k, list):
# 2D list
if not all(isinstance(row, list) for row in k):
return "Invalid input: k must be a scalar or 2D list."
result = []
for row in k:
result_row = []
for val in row:
out = compute(val)
if isinstance(out, str):
return out
result_row.append(out)
result.append(result_row)
return result
else:
return compute(k)