Statistical

Weighted random assignment & Monte Carlo lookup (cumulative lower bounds)

Sample categories with unequal probabilities (e.g., 60% Standard, 30% Silver, 10% Gold) by matching a [0, 1) draw against cumulative lower thresholds.

Start cumulative probability thresholds at 0.00—not P1!

RANDBETWEEN(1, 3) picks each option 33.3% of the time. To simulate real business mixes—like 60% Standard, 30% Silver, and 10% Gold orders—build a Cumulative LOWER Bound column starting at 0: [0.00, 0.60, 0.90] (where Row_i = PriorCumLow + PriorProb).

Any uniform draw u in [0, 1) (from RAND() in live simulations or a saved draw cell) falls in [0.00, 0.60) 60% of the time, [0.60, 0.90) 30% of the time, and [0.90, 1.00) 10% of the time—matched cleanly with XLOOKUP(u, CumLow, Outcome, , -1)!

Outcome (A)	Weight (B)	CumLowerBound (C)	Winning Interval for u in [0, 1)
Standard	0.60 (60%)	0.00	0.00 <= u < 0.60
Silver	0.30 (30%)	0.60 (=C2+B2)	0.60 <= u < 0.90
Gold	0.10 (10%)	0.90 (=C3+B3)	0.90 <= u < 1.00 (Sum B = 1.00)

Weighted probability lookup and 100%-sum audit formulas

Replace D2 with RAND() when generating live Monte Carlo rows, or keep deterministic uniform draws in D2 for reproducible scenario audits.

=IF(OR(D2<0,D2>=1),"Invalid",XLOOKUP(D2, C2:C4, A2:A4, "Invalid", -1))

Audit that probability weights sum to 100%

Check that all three weights are positive numeric values and sum to 1 within the chosen tolerance.

=IF(AND(COUNT(B2:B4)=3,MIN(B2:B4)>0,ABS(SUM(B2:B4)-1)<0.0000001),"Valid 100%","Check Weights")

Inputs and interpretation

Use positive weights summing to 1 within a chosen numerical tolerance and cumulative lower bounds derived in the same order. Zero-weight categories should be removed to avoid duplicate thresholds. Draws must satisfy 0 <= u < 1. A manually entered draw is not a random seed; save draws for reproducibility. RAND is volatile, and finite samples do not exactly reproduce target proportions.

Try the example

Use the original values or change them to test your understanding.