HomeTechniques and Tips@RISK Simulation: Graphical ResultsAutomatic Overlays from Multiple Worksheets

# 7.26. Automatic Overlays from Multiple Worksheets

Applies to: @RISK for Excel 5.x–7.x

I want to have several inputs or outputs overlaid on one graph, to be displayed automatically at the end of a simulation. How can I do it?

You can embed overlay graphs in your worksheet with the RiskResultsGraph( ) function. The graphs will be updated automatically when you run a simulation, and the latest versions will be stored with your workbook even if you don't store simulation results. Please download the attached example (about 130 KB).

To pull together multiple distributions in one overlaid graph, you need a contiguous set of cells (row or column), defined as an output range with a common name. If the cells you want to graph are in different parts of a worksheet, or even in different worksheets, you can create a range of contiguous cells that are set equal to the cells you actually want to graph, and then designate the range as an @RISK output. The example has four such ranges, in column C.

Some additional points about this example:

• The RiskResultsGraph( ) functions are hidden behind the graphs. To make them visible, delete the graphs, use Excel search for RiskResultsGraph in formulas, or press F5 (Go To) and enter F3, O3, F24, or O24.
• The four graphs illustrate four possible formats for the results. (Graphs 1 and 4 are both histograms, but the first is probability density and the last is relative frequency.)
• RiskResultsGraph( ) uses default graph titles, but you can set a title as in cell F3 in the example. Some limited customizations are available; search RiskResultsGraph in the help file.
• The arguments to RiskOutput, in C3:C14, make all of them part of an output range. Note the comma immediately after the opening parenthesis.
• The INDIRECT( ) functions in C3:C14 make the example more general by using worksheet names that are in worksheet cells rather than embedded in the formula. This inhibits Smart Sensitivity Analysis. Therefore, for your specific model, you probably want to replace the INDIRECT( ) functions with plain cell references, like =Sheet11!C45. If you do use INDIRECT( ) functions, you will want to disable Smart Sensitivity Analysis in Simulation Settings » Sampling, which has been done in this example. More about this issue is in the article Found invalid formula ... Continue without Smart Sensitivity Analysis?

An alternative is available in @RISK Professional and Industrial releases 6.2 and newer, if you're willing to use Visual Basic for Applications. The GraphDistribution method takes an Array-type argument that lets you specify non-contiguous cells for overlays. The Automation Guide, in the Help » Developer Kit (XDK) menu, introduces you to VBA programming and gives a couple of examples of that function; complete documentation is in the @RISK XDK Reference in the same menu.

Last edited: 2015-07-14

Downloads

This page was: Helpful | Not Helpful