Home → Troubleshooting → @RISK for Excel: Simulation → "Error!" or #QNAN for Distributions in an IF( ) Function
Applies to: @RISK For Excel 3.5 and newer
In Simulation Data, for some distributions "Error!" is displayed instead of a value. These distributions are used in Excel IF( ) functions, where the distribution functions are sampled or not based on whether the condition is true or false.
The Simulation Data window or report shows the values of the distributions, not the value of the complete cell formula. @RISK only interprets the first argument of an IF statement and any distributions in subsequent arguments are ignored.
"Error!" Is reported for the iterations in which the distribution function was not sampled because the condition that would have triggered its sampling did not occur. The Error! reported for the input pertains only to the separate @RISK distribution function itself. It does not pertain to the entire contents of the cell containing the IF formula. If you want to see the results of calculations from the entire cell, wrap the formula in a RiskMakeInput( ) function if you have @RISK 5.0 or later, or select the cell as an @RISK output.
See also: Alternatives to =IF for Picking Distributions
If you want to see the results of the distribution function separately, but don't want to see any errors reported, you can place the distribution functions in cells all by themselves, and then use cell references to refer to them in the IF( ) functions. But be aware that doing so can throw off the results of a sensitivity analysis, because then you have @RISK distribution functions that are always sampled, but which don't really figure into the calculation of the entire model in those iterations when condition in the IF statement does not call them.
If your objective is merely to hide the "errors", you can set the Collect Distribution Samples option to None, or set it to Inputs Marked with Collect and then don't mark those particular distribution functions for collection, or filter on errors.
Last edited: 2020-01-17