Home → Troubleshooting → @RISK for Excel: Other Issues → Distribution Formula Shows Garbage Characters after the Value
8.18. 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.
With current maintenance, our users can update the software to version 7.6.1 or newer, which fixes this problem. To upgrade your software go to:
https://www.palisade.com/updates/
Without current maintenance, please contact your sales representative at https://www.palisade.com/about/contact_us.asp or follow the steps below:
@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."
Note: The Sort function will be available to all Microsoft 365 uses in July 2020. Presumably that means all dynamic array features will be released too. Microsoft has renamed their channels and added a new one; see the https://docs.microsoft.com/en-us/deployoffice/update-channels-changes.
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?
For @RISK version 7.6.0 or before, you can prevent the spill by making sure there is an @ symbol in the front of your @RISK distribution functions. For example, =@RiskNormal(0,1) instead of =RiskNormal(0,1). If you use the Define Distribution Window to add your functions, @RISK will automatically add this symbol for you. You will only need to add the @ symbol if you type functions in manually.
Dynamic Arrays and IF Statements
If you are having problems with the Dynamic Arrays within an IF statement, please see the suggested format below.
Cell A1: =@RISKNormal (10,1)
Cell A2: =@RISKNormal (20,2)
Cell 3: =IF(G10=1, A1, A2)
Eliminating the distribution definition from within the IF statement and referencing cells containing your @RISK distributions allows the functions to be used as per normal.
Palisade is working on a more comprehensive solution to this issue. This page will be updated when more information is available.
Last edited: 2020-07-24