HomeTroubleshooting@RISK for Excel: Other IssuesDistribution Formula Shows Garbage Characters after the Value

8.17. Distribution Formula Shows Garbage Characters after the Value

Applies to:
@RISK 6.x/7.x with Office 365

I typed a distribution formula into an Excel cell, and @RISK inserted a second cell, with some garbage characters and a long hexadecimal number.

@RISK isn't doing this; it's a new Excel feature.

In September 2018, Microsoft introduced a new feature in Excel 365, called dynamic arrays. When you type a formula that returns an array, Excel "spills" that formula into the appropriate number of rows and columns. Preview of Dynamic Arrays in Excel says "any newly authored formula that returns an array will spill. For instance, entering =A3:A13 into B3 will cause the values in A3:A13 to be spilled into B3:B13."

Since @RISK distribution functions return a two-cell array, Excel spills the formula that you type into the second cell. The first cell gets the value you wanted, and the second cell gets @RISK's internal identification for the distribution, so you see something like the illustration at right.

If the cell to the right of the distribution is not empty, Excel doesn't spill the into it but instead displays a #SPILL error as the result of the distribution function.

These same things happen if you open an existing workbook that contains @RISK distribution functions—you get either garbage characters in the cell to the right of the distribution function, or #SPILL in the cell with the distribution function.

How can I work around this Excel feature?

At this point, unfortunately, there is no practical workaround, and there's no setting in Excel to reinstate the old behavior. If you have a version of Excel with dynamic arrays, you'll need to have an empty cell to the right of the cell where you place an @RISK distribution, so that Excel can spill the @RISK distribution into that second cell. (This applies whether you create the distribution by typing the formula or through one of the @RISK dialogs.)

We're aware of the problem, and we're looking at possible solutions. This page will be updated when more information is available.

Last edited: 2019-01-14

This page was: Helpful | Not Helpful