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 |