PO.RES.MC.Sample


Description

A deterministic Latin-hypercube sample of one uncertain input: n values, one from the middle of each of n equal-probability strata of a normal, lognormal, triangular or uniform distribution, in an order shuffled by a fixed permutation. The same arguments always give the same column, so a probabilistic sheet recalculates to the same answer and its snapshot stays stable. There is no `RAND` or `RANDARRAY` in it. Give each input of a model its own column, each with its own permutation constants, then evaluate the model row by row and summarise the outcome with `PO.RES.MC.Percentiles`. Returns a column of n values in the unit of the distribution's parameters.

Formula

=LAMBDA(Dist, Dist_A, Dist_B, Dist_C, n, Mult, Shift, IF(GCD(Mult, n)<>1, NA(), LET( u, (MOD(Mult*(SEQUENCE(n)-1)+Shift, n)+0.5)/n, IF(Dist=1, NORM.INV(u, Dist_A, Dist_B), IF(Dist=2, LOGNORM.INV(u, Dist_A, Dist_B), IF(Dist=3, IF(u<(Dist_B-Dist_A)/(Dist_C-Dist_A), Dist_A+SQRT(u*(Dist_C-Dist_A)*(Dist_B-Dist_A)), Dist_C-SQRT((1-u)*(Dist_C-Dist_A)*(Dist_C-Dist_B))), IF(Dist=4, Dist_A+u*(Dist_B-Dist_A), NA())))))))

Parameters

Parameter Unit Description
Dist — Distribution: 1 normal, 2 lognormal, 3 triangular, 4 uniform
Dist_A input unit Normal: mean. Lognormal: mean of ln x. Triangular: minimum. Uniform: minimum
Dist_B input unit Normal: standard deviation. Lognormal: standard deviation of ln x. Triangular: most likely. Uniform: maximum
Dist_C input unit Triangular: maximum. Not used by the other distributions (pass 0)
n — Number of values (strata)
Mult — Permutation multiplier a, a whole number with no factor in common with n
Shift — Permutation offset b, a whole number from 0 to n − 1

Examples

Invocation Expected
=PO.RES.MC.Sample(1, 0, 1, 0, 5, 2, 1) [-0.524400513, 0.524400513, -1.281551564, 0, 1.281551564]
=PO.RES.MC.Sample(2, 0, 1, 0, 5, 2, 1) [0.5919101, 1.68944574, 0.27760624, 1, 3.60222447]
=PO.RES.MC.Sample(3, 10, 20, 40, 5, 2, 1) [19.48683298, 26.58359214, 15.47722558, 22.67949192, 32.25403331]
=PO.RES.MC.Sample(4, 10, 20, 0, 5, 2, 1) [13, 17, 11, 15, 19]
=ROWS(PO.RES.MC.Sample(1, 100, 10, 0, 1000, 7, 0)) 1000
=AVERAGE(PO.RES.MC.Sample(1, 100, 10, 0, 1000, 7, 0)) 100
=MEDIAN(PO.RES.MC.Sample(1, 100, 10, 0, 1000, 7, 0)) 100
=AVERAGE(PO.RES.MC.Sample(2, 4.6, 0.3, 0, 1000, 11, 101)) 104.0562768
=MEDIAN(PO.RES.MC.Sample(2, 4.6, 0.3, 0, 1000, 11, 101)) 99.48432267
=AVERAGE(PO.RES.MC.Sample(3, 10, 20, 40, 1000, 13, 211)) 23.33331952
=MEDIAN(PO.RES.MC.Sample(3, 10, 20, 40, 1000, 13, 211)) 22.67949409
=AVERAGE(PO.RES.MC.Sample(4, 10, 20, 0, 1000, 17, 307)) 15
=MEDIAN(PO.RES.MC.Sample(4, 10, 20, 0, 1000, 17, 307)) 15
=IF(ISERROR(PO.RES.MC.Sample(1, 0, 1, 0, 6, 2, 1)), 1, 0) 1

Need a custom LAMBDA? Suggest a New Lambda