Why Do Some Distributions Show "Error!" or #QNAN in IF() Functions in @RISK?

Question:
In Simulation Data, some distributions display "Error!" instead of a value when used in Excel IF() functions. What does this mean and how can I resolve it?

Answer:

• @RISK only evaluates the first argument of an IF() statement.

• Distribution functions in the true/false branches are ignored if the condition does not trigger them.

• "Error!" or #QNAN appears for iterations where a distribution was not sampled.

• This error is specific to the distribution function, not the entire cell formula.

Solutions

1. See full cell results

   • Use RiskMakeInput() (available in @RISK 5.0+) or mark the cell as an @RISK output.

2. Avoid showing errors

   • Place distributions in separate cells and reference them in IF().

   • Note: This may affect sensitivity analysis, as distributions are always sampled even if unused.

3. Hide errors without changing calculations

   • Set Collect Distribution Samples to None.

   • Or select Inputs Marked with Collect and do not mark these distributions.

   • Alternatively, filter out errors in reports.

 

Related Article:

Alternatives to =IF for Picking Distributions