Home → Troubleshooting → @RISK for Excel: Simulation → "The cell ... is part of an array formula."
Applies to:
@RISK 5.x–7.x
RISKOptimizer 5.x
When I run my simulation, a message pops up saying
The cell ... is part of an array formula. @RISK outputs cannot be part of array formulas.
Or
The cell ... is part of an array formula. @RISK distributions cannot be part of array formulas.
When I click OK, the simulation is abandoned. What is wrong, and how can I fix it?
To speed up the calculations, at start of simulation @RISK replaces formulas that include RiskOutput( ) and other @RISK functions with special formulas; then at the end of simulation is resets them to the formulas that you entered. @RISK can't do this replacement with an array formula, and therefore you get this message.
Exceptions: RiskData( ) can be used effectively in array formulas; see Placing Iteration Data in Worksheet with RiskData( ). Also, @RISK's Time Series Fit and Batch Fit commands create the time series distributions as array formulas.
There are two workarounds:
Maybe you don't actually need an array formula. For example, many array formulas are best replaced by Excel's SUMPRODUCT( ) function, which will also execute faster. Multiple Condition Tests by Bob Phillips specifically addresses the use of SUMPRODUCT( ) to replace filtering conditions like SUMIF( ) and COUNTIF( ).
If you do need an array formula, move the RiskOutput or to a new cell. For example, suppose that your cell AA17 contains
={RiskOutput()+formula}
Remove the "RiskOutput()+" part, and then close the formula by pressing Ctrl-Shift-Enter. Cell AA17 will now contain
={formula}
In a blank cell, place this formula:
=RiskOutput()+AA17
This is a regular non-array formula that references the cell that contains the array formula. Thus @RISK will still capture all the numbers for your output value, and you avoid the message.
If you have created an array formula that involves an @RISK distribution or statistics function other than RiskData( ), again you will need to pull the @RISK function out of the array formula. The exact change will depend on the logic of your worksheet.
Last edited: 2015-10-15