VAL_DISCRETE

Overview

The VAL_DISCRETE function selects a value from a list based on a given discrete probability distribution. This is useful for simulations, random sampling, and business scenarios where outcomes are determined by weighted probabilities. A discrete probability distribution assigns a probability $p_i$ to each possible outcome $x_i$ such that:

The function implements random selection by drawing a single value $x_k$ from the set $\{x_1, x_2, ..., x_n\}$, where the probability of selecting $x_k$ is $p_k$:

This approach is widely used in Monte Carlo simulations, scenario analysis, and probabilistic modeling, enabling users to automate random selection in Excel based on business-defined likelihoods.

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

Usage

To use the function in Excel, enter it as a formula in a cell, specifying the values and their associated probabilities:

=VAL_DISCRETE(values, distribution)

• values (2D list, required): List of possible values to select from.
• distribution (2D list, required): List of probabilities (must sum to 1).

The function returns a single value (string or number) selected according to the provided probability distribution. If the input is invalid, it returns None.

Examples

Example 1: Simulate Customer Type

Select a customer type based on the following values and probabilities:

In Excel:

=VAL_DISCRETE(A1:C1, A2:C2)

Expected output: Returns “Retail” 60% of the time, “Wholesale” 30% of the time, “Online” 10% of the time.

Example 2: Select Project Outcome

Select a project outcome based on the following values and probabilities:

In Excel:

=VAL_DISCRETE(A1:B1, A2:B2)

Expected output: Returns “Success” 80% of the time, “Failure” 20% of the time.

Python Code

def val_discrete(values, distribution):
    """
    Select a value from a list based on a discrete probability distribution.
 
    Args:
        values: 2D list of possible values to select from.
        distribution: 2D list of probabilities (must sum to 1).
 
    Returns:
        The value selected according to the weights, or None if input is invalid.
 
    This example function is provided as-is without any representation of accuracy.
    """
    import random
    if not values or not distribution:
        return None
    if not isinstance(values, list) or not isinstance(distribution, list):
        return None
    if not values or not values[0] or not distribution or not distribution[0]:
        return None
    if len(values[0]) != len(distribution[0]):
        return None
    weights = distribution[0]
    try:
        total = sum(float(w) for w in weights)
        if abs(total - 1.0) > 0.01:
            return None
    except Exception:
        return None
    try:
        return random.choices(values[0], weights=weights)[0]
    except Exception:
        return None

Live Demo

Example Workbook

Link to Workbook