RANDINT

Overview

The RANDINT function computes values related to the Uniform discrete distribution, which describes a random variable that takes integer values between low (inclusive) and high (exclusive) with equal probability. 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 provides the RANDBETWEEN function, which generates a random integer between two values. However, the Python function in Excel provided here supports additional features such as the 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.randint documentation .

Usage

To use the function in Excel:


=RANDINT(k, low, high, [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. For statistics modes, this is ignored and can be set to 1.
low (int, required): Lower bound (inclusive).
high (int, required): Upper bound (exclusive).
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=5, low=1, high=10

Inputs:

k	low	high	mode	loc
5	1	10	pmf	0

Excel formula:


=RANDINT(5, 1, 10, "pmf", 0)

Expected output:

Result
0.1111

Example 2: CDF at k=5, low=1, high=10

Inputs:

k	low	high	mode	loc
5	1	10	cdf	0

Excel formula:


=RANDINT(5, 1, 10, "cdf", 0)

Expected output:

Result
0.5556

Example 3: Survival Function at k=5, low=1, high=10

Inputs:

k	low	high	mode	loc
5	1	10	sf	0

Excel formula:


=RANDINT(5, 1, 10, "sf", 0)

Expected output:

Result
0.4444

Example 4: Inverse CDF (ICDF) for probability k=0.5, low=1, high=10

Inputs:

k	low	high	mode	loc
0.5	1	10	icdf	0

Excel formula:


=RANDINT(0.5, 1, 10, "icdf", 0)

Expected output:

Result
5

Example 5: Mean, Variance, Std, Median

Inputs:

k	low	high	mode
1	1	10	mean
1	1	10	var
1	1	10	std
1	1	10	median

Excel formulas:


=RANDINT(1, 1, 10, "mean", 0)
=RANDINT(1, 1, 10, "var", 0)
=RANDINT(1, 1, 10, "std", 0)
=RANDINT(1, 1, 10, "median", 0)

Expected outputs:

Result
5
6.6667
2.582
5

Python Code


from scipy.stats import randint as scipy_randint
 
def randint(k, low, high, mode="pmf", loc=0):
    """
    Compute Uniform discrete distribution values: PMF, CDF, SF, ICDF, ISF, mean, variance, std, or median.
 
    Args:
        k: Value(s) at which to evaluate (float or 2D list).
        low: Lower bound (inclusive, int).
        high: Upper bound (exclusive, int).
        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 low, high
    try:
        low_val = int(low)
        high_val = int(high)
        if not (low_val < high_val):
            return "Invalid input: low must be less than high."
    except Exception:
        return "Invalid input: low and high must be integers."
    # 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_randint.mean(low_val, high_val, loc=loc_val)
    if mode == "var":
        return scipy_randint.var(low_val, high_val, loc=loc_val)
    if mode == "std":
        return scipy_randint.std(low_val, high_val, loc=loc_val)
    if mode == "median":
        return scipy_randint.median(low_val, high_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_randint.pmf(kval, low_val, high_val, loc=loc_val))
        elif mode == "cdf":
            return float(scipy_randint.cdf(kval, low_val, high_val, loc=loc_val))
        elif mode == "sf":
            return float(scipy_randint.sf(kval, low_val, high_val, loc=loc_val))
        elif mode == "icdf":
            return float(scipy_randint.ppf(kval, low_val, high_val, loc=loc_val))
        elif mode == "isf":
            return float(scipy_randint.isf(kval, low_val, high_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