About array functions in Excel

Download a complete copy of this risk analysis resource for free here.

There are many Excel and ModelRisk functions that have an output of multiple spreadsheet cells. Such functions are called array functions.

For example, the ModelRisk functions to generate random values from multivariate distributions, time series and copulas are array functions.

To insert an array function follow these steps:

Select the appropriate number of cells you want the array function's output sent to (by dragging the mouse over them).
Type the array formula in Excel's formula bar (e.g. =ROW(1:4))
Press CTRL+SHIFT+ENTER to insert the array function. The function is now inserted in all the selected cells. Excel automatically inserts curly brackets {} around the formula to indicate it is an array function.

To indicate an array formula in example model screenshots, we put curly brackets around it, like this:

{=VoseCopulaBiClayton(3)}

Keep in mind that you should follow the steps outlined above to insert such a function into a spreadsheet yourself.

Example: correlating variables with a bivariate copula

In ModelRisk, copulas are used to control the sampling of univariate distributions via the optional U-parameter. So for example, to generate a normal(0,1) and a beta(2,1) value correlated by a Clayton(3) copula, you would do the following:

Select the A1 and B1 spreadsheet cells.
Type =VoseCopulaBiClayton(3) in the Excel formula bar and press CTRL+SHIFT+ENTER - Excel now inserts this as an array function over the two selected cells, indicated by curly brackets. Two random samples from the copula are generated.
Insert =VoseNormal(0,1,A1) in the cell A2, and =VoseBeta(2,1, B1) in the cell B2. The cell references are U parameters that refer to the copula values generated in the first cell.
Now the A2 and B2 cell contain random values correlated by the copula.

What you are perhaps more used to are functions that take an array (i.e. a range of cells) as input argument(s). Again, this is indicated with curly brackets, like in the following formula:

=SUM({ListOfNumbers})

Array functions in a single cell

There is another use of array functions that in fact only reside in a single cell. For example: if you have a data set named 'Data' in your spreadsheet, you could calculate the quantity:

in one array formula, like this:

{=SUM((Data-AVERAGE(Data))^3)}

By typing: "=SUM((Data-AVERAGE(Data))^3)" and then using CTRL+SHIFT+ENTER to enter the formula you force Excel to consider this as an operation performed on each element of the array Data.

Be careful with this type of array formula because Excel can do some unpredictable things. On one hand we have the advantage of a more compact model (you will see some model examples here using this type of array function to keep the model small) but on the other, there is a greater risk of introducing errors. We recommend you at least replicate the calculation in columns of cells and check the outputs give the same result. Curiously, array functions of this type are often actually slower to calculate in Excel than the long version.