Home → Techniques and Tips → VBA Programming with @RISK → Alternatives to =IF for Picking Distributions
Applies to:
@RISK 6.2 and newer, Professional and Industrial Editions
I want to set up my worksheet so that I can use different distributions depending on a code. I've got a bunch of formulas like this:
=IF(C47=1,RiskBinomial(D47,E47), IF(C47=2,RiskPert(F47,G47,H47), IF(C47=3,RiskLognorm(I47,J47), RiskTriang(K47,L47,M47))))
Is there any reason not to do it this way? Is there an alternative that might be more efficient?
There are several reasons why IFs in the worksheet are not the best way to model a choice of distribution. You'll have spurious entries in your Model Window, your Simulation Data window and report, etc. You'll also see error values for each distribution in all the iterations where it's not selected. Also, having four times as many distributions will definitely slow down your simulation, but whether it will slow it down by enough to matter depends on how many distributions there are, what the rest of your model looks like, and how many iterations you've chosen.
A quick-and-dirty possibility is to wrap such formulas inside a RiskMakeInput function. It's quick to do, though it does add another layer and it doesn't address the efficiency issue. But at least it gets rid of the spurious data collection. For the formula above, a RiskMakeInput would look like this:
=RiskMakeInput( IF(C47=1,RiskBinomial(D47,E47), IF(C47=2,RiskPert(F47,G47,H47), IF(C47=3,RiskLognorm(I47,J47), RiskTriang(K47,L47,M47))))
RiskMakeInput is very powerful and has many uses. For some examples, see Combining Inputs in a Sensitivity Tornado; Excluding an Input from the Sensitivity Tornado; Same Input Appears Twice in Tornado Graph. See also: All Articles about RiskMakeInput.
Probably a cleaner approach is to move that logic into a macro, where it is executed once only. The attached example contains such a macro, linked from a button in the worksheet.
For the sake of illustration, this worksheet is set up with a choice of seven distributions: triangular (RiskTriang and RiskTrigen), Pert (RiskPert), uniform (RiskUniform), normal (RiskNormal), log-normal (RiskLognorm), and Johnson (RiskJohnsonMoments). In column K, you specify which distribution to use for each risk. The parameters are in columns B through J, and cells N2:O2 give the row numbers to be handled by the macro.
When you click the worksheet button, the macro looks at each entry in column K and writes the appropriate distribution and parameters in column L. The macro includes a RiskName property function referring to the risk name given in column A. If any of the cells in column K contain incorrect distribution names, the macro displays an error message; otherwise, it runs a simulation. If you want to run further simulations without changing distributions, click the Start Simulation button in @RISK or click the button in the worksheet, but you must use the worksheet button after changing any distributions in column K.
Last edited: 2018-02-20