Excel Dynamic Arrays

The Excel Dynamic Array feature introduced in the Office 365 “Insider” channel in late 2018, and scheduled to be publicly released for all Office 365 users in 2020 significantly affects how custom add-in functions (such as all of @RISK’s functions: RiskNormal, RiskUniform, etc.) will appear and behave in various circumstances. It’s important to note that all custom functions from all add-in vendors are subject to these same rules.

While @RISK itself does not use Dynamic Arrays in its functions, the changes Microsoft introduced to support them do affect them indirectly. These changes are centered around what happens when a model created in Excel DA (Dynamic Array) is opened in Excel SA (Standard Array), or vice-versa. For the vast majority of situations, this can be considered to be cosmetic in nature, although in some cases the results can be confusing.

In order to aid in the following discussions the following abbreviations will be used:

• Excel DA – A version of Excel that supports the new dynamic array (DA) feature.
• Excel SA – A version of Excel that only supports only the older “Standard Arrays” (SA).
• CSE Array – A non-dynamic array that uses the “Ctrl-Shift-Enter” method of entry.

The @ Symbol

The '@' symbol (no quotes) is used in Excel DA to indicate that a function should use what is called “implicit intersection”. A detailed discussion of implicit intersection is beyond the scope what can be covered here, but be aware that Excel will sometimes automatically prepend @RISK functions with the @ symbol. This is part of its complicated efforts to maintain compatibility between Excel DA and Excel SA.

Keep in mind these two principles:

1. If Excel has automatically prepended an @RISK function with the @ symbol, it has no effect on the mathematical behavior of the model.
2. It is not necessary for an @RISK user to add the @ symbol before any @RISK function in a model. If the symbol is added, the only impact it will have is on how that function will appear if and when that model is later opened in Excel SA.

Models Created in Excel SA Opened in Excel DA

For a model originally created in Excel SA, and then later opened in Excel DA, Excel will automatically prepended the @ symbol to all non-array @RISK functions. Thus, a model that originally looked like this in Excel SA:

will appear in Excel DA as:

The @ symbols have no impact on any of the @RISK functionality.

Models Created in Excel DA Opened in Excel SA

For a model created in Excel DA and later opened in Excel SA, Excel will automatically change all @RISK function that do not have a @ prefix into single cell CSE arrays. Thus, a model that originally looks like this in Excel DA:

will look like this in Excel SA (note the braces around the highlighted function which indicate the presence of a CSE array):

@RISK has been modified to support CSE Arrays of this nature, so again this can be considered cosmetic.

@RISK Example Models

In order to make a single set of example models that are supported in both Excel DA and Excel SA, all @RISK example models were written in Excel SA. Thus, the @ prefix will appear on every @RISK function in those models.

Using Dynamic Arrays in @RISK Models

While @RISK functions themselves don’t make use of dynamic arrays, other dynamic array functions can be used in an @RISK model. However, one potential complication involves @RISK’s Smart Sensitivity Analysis feature.

Smart Sensitivity Analysis makes it possible to exclude inputs from sensitivity results that don’t have a functional connection to the output being analyzed, thus removing extraneous noise and also inputs that might have a strong correlation, but non-causal relationship, with the output. This works by recursively tracing the precedents of the output and finding every input that can affect it.

The dynamic array feature of Excel has complicated this process because now the precedents of an output can potentially change dynamically during the simulation. Thus, when using dynamic arrays, we recommend that either:

1. Care is taken to ensure the model always contains a path from output to input that Excel’s precedence tracing can follow.
2. Smart Sensitivity Analysis is turned off.