BINOM

Overview

The BINOM function computes values related to the Binomial distribution, a discrete probability distribution that describes the number of successes in a fixed number of independent Bernoulli trials, each with the same probability of success. 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 Functions Comparison:

BINOM.DIST: Computes the PMF or CDF for the binomial distribution. Only supports scalar input and does not provide the survival function, inverse CDF, inverse SF, or distribution statistics.
BINOM.DIST.RANGE: Computes the probability of a range of outcomes. Limited to range probabilities.
BINOM.INV: Computes the inverse CDF (quantile function). Only supports quantile, not other statistics.

Python Excel Function Advantages:

Supports PMF, CDF, SF, inverse CDF (ICDF), inverse SF (ISF), mean, variance, standard deviation, and median.
Accepts scalar or 2D array input for batch calculations.
Provides location parameter for shifting the distribution.
Returns distribution statistics directly.

For more details, see the scipy.stats.binom documentation .

This example function is provided as-is without any representation of accuracy.

Usage

To use the function in Excel:


=BINOM(k, n, p, [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 event count. For statistics modes, this is ignored and can be set to 0.
n (int, required): Number of trials (must be >= 0).
p (float, required): Probability of success (0 <= p <= 1).
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=3, n=10, p=0.5

Inputs:

k	n	p	mode	loc
3	10	0.5	pmf	0

Excel formula:


=BINOM(3, 10, 0.5, "pmf", 0)

Expected output:

Result
0.1172

Example 2: CDF at k=3, n=10, p=0.5

Inputs:

k	n	p	mode	loc
3	10	0.5	cdf	0

Excel formula:


=BINOM(3, 10, 0.5, "cdf", 0)

Expected output:

Result
0.1719

Example 3: Survival Function at k=3, n=10, p=0.5

Inputs:

k	n	p	mode	loc
3	10	0.5	sf	0

Excel formula:


=BINOM(3, 10, 0.5, "sf", 0)

Expected output:

Result
0.8281

Example 4: Inverse CDF (ICDF) for probability k=0.5, n=10, p=0.5

Inputs:

k	n	p	mode	loc
0.5	10	0.5	icdf	0

Excel formula:


=BINOM(0.5, 10, 0.5, "icdf", 0)

Expected output:

Result
5

Example 5: Mean, Variance, Std, Median

Inputs:

n	p	mode
10	0.5	mean
10	0.5	var
10	0.5	std
10	0.5	median

Excel formulas:


=BINOM(0, 10, 0.5, "mean", 0)
=BINOM(0, 10, 0.5, "var", 0)
=BINOM(0, 10, 0.5, "std", 0)
=BINOM(0, 10, 0.5, "median", 0)

Expected outputs:

Result
5
2.5
1.5811
5

Python Code


from scipy.stats import binom as scipy_binom
 
def binom(k, n, p, mode="pmf", loc=0):
    """
    Compute 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, >=0).
        p: Probability of success (float, 0<=p<=1).
        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
    try:
        n_val = int(n)
        if n_val < 0:
            return "Invalid input: n must be >= 0."
    except Exception:
        return "Invalid input: n must be an integer."
    # Validate p
    try:
        p_val = float(p)
        if not (0 <= p_val <= 1):
            return "Invalid input: p must be between 0 and 1."
    except Exception:
        return "Invalid input: p must be a number."
    # 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_binom.mean(n_val, p_val, loc=loc_val)
    if mode == "var":
        return scipy_binom.var(n_val, p_val, loc=loc_val)
    if mode == "std":
        return scipy_binom.std(n_val, p_val, loc=loc_val)
    if mode == "median":
        return scipy_binom.median(n_val, p_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_binom.pmf(kval, n_val, p_val, loc=loc_val))
        elif mode == "cdf":
            return float(scipy_binom.cdf(kval, n_val, p_val, loc=loc_val))
        elif mode == "sf":
            return float(scipy_binom.sf(kval, n_val, p_val, loc=loc_val))
        elif mode == "icdf":
            return float(scipy_binom.ppf(kval, n_val, p_val, loc=loc_val))
        elif mode == "isf":
            return float(scipy_binom.isf(kval, n_val, p_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)

Live Demo

Example Workbook

Link to Workbook