HomeTechniques and Tips@RISK DistributionsCell References in Distributions

3.2. Cell References in Distributions

Applies to:  @RISK 4.x–7.x

Must I specify the X's and P's as fixed numbers in the RiskDiscrete, RiskCumul, RiskCumulD, RiskDUniform, RiskHistogrm, or RiskGeneral distribution, or can I replace them with cell references?  What about RiskSimtable — can I use cell references instead of fixed numbers?

You can replace the list of numbers with cell references without braces, but the referenced cells must be a contiguous array in a row or a column.  It's not possible to collect cells from multiple locations in the workbook.

Example 1:

=RiskCumul(0, 10, {1,5,9}, {.1,.7,.9})

If the probabilities are in cells C1, C2, C3, then you replace the second set of braces and numbers with an array reference, like this:

=RiskCumul(0, 10, {1,5,9}, C1:C3)

If the numbers (X values) are in cells D1, D2, D3, then you replace the first set of braces and numbers with an array reference, like this:

=RiskCumul(0, 10, D1:D3, {.1,.7,.9})

And you can replace both the X's and the P's with array references, like this:

=RiskCumul(0, 10, D1:D3, C1:C3)

Example 2:

=RiskSimtable({10,20,30,40})

If the scenario numbers are in cells IP201 through IS201, then you replace the braces and numbers with an array reference, like this:

=RiskSimtable(IP201:IS201)

These rules might seem arbitrary, but they're actually standard Excel.  The @RISK distribution functions mentioned above take one or two array arguments.  Excel lets you specify a constant array, which is a series of numbers enclosed in braces; or you can specify a range of cells, which is a contiguous series of cells in one row or one column. Excel doesn't have any provision for making an array out of scattered cells.

Additional keywords:  Simtable, Discrete distribution, Cumul distribution, CumulD distribution, DUniform distribution, Histogram distribution, General distribution

Last edited: 2015-06-19

This page was: Helpful | Not Helpful