Home → Troubleshooting → @RISK for Excel: Other Issues → Results Not as Expected (Debugging Tips)
Applies to: @RISK 5.x–7.x
Contents:
"Errors" that really aren't?
Error message appears?
Output has just one value?
No values to graph?
Calculations seem wrong
Visual Basic errors?
See also:
"No inputs found that affect this output" instead of Tornado Graph
My simulation results in @RISK don't seem right. Can you recommend any techniques for finding my mistakes in my model?
Consider the possibility that your model is actually behaving correctly. We're frequently asked about these "errors", but they make sense when they're explained:
If numbers are different when you tap F9 than when you run a simulation, that's normal. F9 uses Monte Carlo sampling, even if your Simulation Settings specify the default Latin Hypercube. Also, F9 doesn't honor correlations.
If your worksheet shows the means of inputs, but not the means of outputs, that too is normal; see Static Value of Output Differs from Simulated Mean. There are special @RISK functions for Placing Simulation Statistics in a Worksheet.
Users are sometimes concerned when a Sum of Percentiles Differs from Percentile of the Sum, but this is normal. Please see the article for the explanation.
If some iterations of an input show errors, and the input is part of a cell formula with IF() or other conditional logic, that may be normal. See An input distribution shows "no values to graph", or it shows a graph but the statistics table shows that some iterations were errors, below.
If an error message appears, there's probably an explanation and a fix in the Troubleshooting section of this Knowledge Base. Type a few key words in the search window at the bottom of this page.
My output graph is just a vertical line, and the statistics table shows minimum = mean = maximum.
This output has the same value in every iteration. Most likely, that means none of your input distributions are precedents of this output.
To test this, click the "rolling dice" Random/Static icon, in the ribbon below the number of simulations, and then repeatedly tap the F9 key. You will see which cells change and which don't, and that will help you find where the chain of dependents is interrupted.
For pure Excel models, as opposed to projects with associated MPP files, you can trace precedent-dependent relationships directly by using tools in Excel. To find the precedents of an output, click the output cell and then, on Excel's Formulas tab, click Trace Precedents repeatedly. Each click will add arrows for one further level of precedents. If there are too many arrows, you can click Remove Arrows, then click on one particular precedent, and then Trace Precedents back from that point.
In a similar way, you can click on an input cell and then use Trace Dependents to find the cells that depend on that input cell.
For project models with associated MPP files, Schedule Audit will tell you of tasks that have no predecessors or no successors. This can help you trace where the logic of your project needs correction.
An input distribution shows "no values to graph", or it shows a graph but the statistics table shows that some iterations were errors.
Take a look at the cell that contains the distribution. Many people assume that the values shown on a histogram, or in the Data Window, are values of the cell, but actually they're the values of the distribution within the cell. If some logic causes the distribution to be skipped in some iterations, then @RISK records a value of "Error" for the distribution during those iterations. For example, suppose that the cell formula is
=IF(A15>0, RiskTriang(100,200,450), 0)
By this logic, a value is drawn from the distribution when A15 is greater than 0, but not when A15 is less than or equal to 0. In each iteration when A15 ≤ 0, @RISK records "Error" because the distribution is not called and therefore has no value.
If you actually want @RISK to record the values of a cell formula, and not just the values of a distribution within the cell, wrap it in a RiskMakeInput function, like this:
=RiskMakeInput( IF(A15>0, RiskTriang(100,200,450), 0) )
An output shows "no values to graph", or it shows a graph but the statistics table shows that some iterations were errors.
Either way, here are some tools:
Click the "rolling dice" Random/Static icon, in the ribbon below the number of simulations, and then repeatedly tap the F9 key. You will see which cells take on errors and which don't, and that will help you find where random values in your distributions create problems.
In Simulation Settings, on the View tab, tick the box labeled Pause on Output Errors, then run a simulation. @RISK will pause at the first occurrence of an output error, and display a list of precedent inputs. You can also look around in your worksheet, because it contains the input values for this iteration, and calculations derived from those values.
You can also use "shoeprint mode" to find the errors; see Some Iterations Show Error in Data Window. What Can I Do?
If you're running a simulation with multiple CPU, and you have errors in blocks of consecutive iterations but numbers in other blocks of consecutive iterations, you probably have the problem described in Multiple CPU — Blocks of Errors in Simulated Outputs. That article explains what to look for.
Numbers seem wrong in my calculated results.
Here are some suggestions. (Save the workbook under a new name before you make debugging-type edits.)
Use the Simulation Data window to show all iterations. But first, reduce the number of iterations to about 3 and run a new simulation, so that you don't have an overwhelming mass of data to deal with.
Break up complicated formulas. For example, if you have IF(A, B, C), and A, B, and C are complicated formulas, try putting them in separate cells and then use cell references in the =IF formula.
Consider adding some cells as outputs, just so that you can see their values in every iteration. This can be a great way to narrow down a calculation error.
Force one or more inputs to bad values. For example, if you have a RiskNormal(5,5), and you suspect that a negative value may create error conditions, change it to RiskNormal(–2,0). That will cause every iteration to get a –2 from this input.
I think my worksheets are okay, but my VBA code isn't working right.
Debugging Visual Basic for Applications code can be tricky. Here are some hints:
Misspelled variable names are a potential problem. To find them, put Option Explicit before the first sub or function in your module, and then click Debug » Compile. If there are multiple errors, the compiler shows you only the first, so after you fix each error you need to click Debug » Compile again.
Try setting breakpoints, and then stepping through the code execution. That will tell you pretty closely where the error is coming from. You can then use the Immediate Window (Ctrl+G) to display values of variables and diagnose the problem.
Another technique is to use MsgBox functions to pop up progress messages. As an alternative, the Debug.Print method will write anything you like to the Immediate Window, so that you won't have to deal with pop-ups but still can monitor progress.
Can you look at my model and tell me what's wrong?
We're sorry, but the scope of free Tech Support services doesn't include debugging customer models. If you have a question about how a particular @RISK feature works, or if @RISK is not behaving correctly, Tech Support will gladly help. If you want a more detailed review or audit of your model, please ask your Palisade sales manager to book some time with one of our custom developers or industry consultants.
Additional keywords: Precedent and dependent cells, precedent and dependant cells
Last edited: 2019-02-22