VAL_DISCRETE
Overview
The VAL_DISCRETE function performs weighted random selection from a finite set of candidate values. It maps each candidate to a probability weight and returns one sampled outcome, making it useful for modeling categorical uncertainty in spreadsheets. This is especially practical when scenarios are not uniformly likely and reproducible sampling is needed through a fixed seed.
Mathematically, it samples from a discrete probability mass function over values v_1, v_2, \dots, v_n with probabilities p_1, p_2, \dots, p_n, where each p_i \ge 0 and the probabilities approximately sum to 1:
\Pr(X = v_i) = p_i, \quad i=1,\dots,n, \quad \sum_{i=1}^{n} p_i \approx 1.
For repeated draws with replacement, each draw is independent and follows the same distribution, so expected category frequencies converge to the specified probabilities as sample size increases.
This implementation wraps Python’s random.choices from the random standard library. Key inputs are the value list, a same-length weight list, and an optional seed; internally, the function validates dimensions, checks that weights are numeric and near a total probability of 1, then returns the selected item as text.
Weighted discrete sampling is common in risk analysis, Monte Carlo simulation, operations research, and product analytics where outcomes are categorical rather than continuous. Typical uses include demand-state simulation, customer-segment assignment, and probabilistic scenario testing in financial or planning models.
This example function is provided as-is without any representation of accuracy.
Excel Usage
=VAL_DISCRETE(values, distribution, seed)values(list[list], required): 2D array of possible values (numeric or text) to select from.distribution(list[list], required): 2D array of probabilities (must sum to approximately 1.0).seed(int, optional, default: null): Optional seed for the random number generator to ensure reproducible results.
Returns (str): The selected value from the provided list, returned as a string.
Example 1: Select from three categories
Inputs:
| values | distribution | seed | ||||
|---|---|---|---|---|---|---|
| Apple | Banana | Cherry | 0.1 | 0.8 | 0.1 | 42 |
Excel formula:
=VAL_DISCRETE({"Apple","Banana","Cherry"}, {0.1,0.8,0.1}, 42)Expected output:
"Banana"
Example 2: Select from numeric values
Inputs:
| values | distribution | seed | ||||||
|---|---|---|---|---|---|---|---|---|
| 10 | 20 | 30 | 40 | 0.25 | 0.25 | 0.25 | 0.25 | 123 |
Excel formula:
=VAL_DISCRETE({10,20,30,40}, {0.25,0.25,0.25,0.25}, 123)Expected output:
"10"
Example 3: Support vertical ranges
Inputs:
| values | distribution | seed |
|---|---|---|
| Low | 0.2 | 7 |
| Medium | 0.6 | |
| High | 0.2 |
Excel formula:
=VAL_DISCRETE({"Low";"Medium";"High"}, {0.2;0.6;0.2}, 7)Expected output:
"Medium"
Example 4: Handle near-1.0 sum
Inputs:
| values | distribution | seed | ||
|---|---|---|---|---|
| A | B | 0.333 | 0.667 | 1 |
Excel formula:
=VAL_DISCRETE({"A","B"}, {0.333,0.667}, 1)Expected output:
"A"
Python Code
Show Code
import random
def val_discrete(values, distribution, seed=None):
"""
Select a value from a list based on a discrete probability distribution.
See: https://docs.python.org/3/library/random.html#random.choices
This example function is provided as-is without any representation of accuracy.
Args:
values (list[list]): 2D array of possible values (numeric or text) to select from.
distribution (list[list]): 2D array of probabilities (must sum to approximately 1.0).
seed (int, optional): Optional seed for the random number generator to ensure reproducible results. Default is None.
Returns:
str: The selected value from the provided list, returned as a string.
"""
try:
def to2d(x):
return [[x]] if not isinstance(x, list) else x
# Normalize inputs
val_list = to2d(values)
dist_list = to2d(distribution)
# Flatten 2D lists to 1D
flat_vals = [item for row in val_list for item in row]
flat_dists = [item for row in dist_list for item in row]
if not flat_vals:
return "Error: The values parameter cannot be empty."
if not flat_dists:
return "Error: The distribution parameter cannot be empty."
if len(flat_vals) != len(flat_dists):
return "Error: The values and distribution parameters must have the same number of elements."
# Validate and parse weights
try:
weights = [float(w) for w in flat_dists]
total = sum(weights)
if abs(total - 1.0) > 0.02:
return f"Error: Probabilities must sum to approximately 1.0 (current sum: {total:.4f})."
except (TypeError, ValueError):
return "Error: All elements in the distribution parameter must be numeric."
# Initialize random number generator
rng = random.Random(seed)
# Select value
result = rng.choices(flat_vals, weights=weights, k=1)[0]
return str(result)
except Exception as e:
return f"Error: {str(e)}"