Anyone whose decision-making and livelihood depend on robust and dependable spreadsheet needs to know and care about dynamic arrays.

In this blog, we focus on the two main issues regarding dynamic arrays:

• the compatibility between Excel versions;
• the uncertainty of how a value was calculated.

We then look at how our newest release of OAK can protect you from drawing unsound conclusions from misleading numbers.

Issues with dynamic arrays

Dynamic arrays can appear where none existed before

Microsoft has made heroic efforts to allow spreadsheets to be transferred between the versions of Excel that support dynamic arrays and older ones that didn’t. However, it is not perfect and becomes a problem when different parties using the same spreadsheet (eg. a client and their advisor) are on various versions of Excel.

While you may scrutinise a model built in Excel 2019 that you have opened in an older version of the software, expecting to find some bugs, you might not be as vigilant when loading an old model into new versions of Excel. However, dynamic arrays can occasionally appear and alter the numbers in the spreadsheet.

So, whether you are using dynamic arrays or not, you must check if the workbook you received contains some and identify their location to ensure that they are as intended.

Is this cell empty?

Previously, the values that resulted from formulae could change at run time when the spreadsheet was recalculated. However, the position where those values appeared on the worksheet was fixed at design time by the location of the formulae. When using dynamic arrays, the location is determined afresh every time the end-user recalculates the spreadsheet.

While this approach brings flexibility that can be useful, it comes at a cost. Previously, cells were either empty or contained a value or a formula. Dynamic arrays have created an intermediate position, where empty cells can display a value spilled out of a dynamic array formula above it and/or to its left.

The source of a cell value can now be ambiguous.

A cell will often be in the potential spill range of just one nearby formula. However, a cell can be in range of several spilling formulae and there’s no way to predict which one affected the value. It depends on the sequence in which the end-user has applied the inputs to the worksheet.

From one run of a model to the next, the size of dynamic arrays can vary, even without any changes to the formula. Therefore, it is essential to understand the structure of the spreadsheet, identify and locate dynamic arrays and potential spilling.  OAK 5 can assist in achieving this.

How OAK 5 helps you solve the dynamic arrays problem

The most popular features in OAK 4, allow you to:

• summarise a workbook, listing all the formulae in it, and their location; assess the riskiness of each formula;
• compare two workbooks;
• and create a map that gives a birds-eye view of a workbook.

All these functions rely on the notion that a formula can be identified with a particular cell on a worksheet, which is no longer the case. The move to dynamic arrays means that a report has the potential to change if the model inputs are altered.

Therefore, it is now possible for two worksheets to be identical as to their formulas, but, due to a small change in their input assumptions, have dynamic array results of very different range dimensions.

Consequently, creating a list of addresses for spreadsheet cells and their content has become meaningless. That’s why in OAK 5, we have modified the reports to list which formulae are active in which cells given the current inputs. Thus, OAK 5 provides consistency with the OAK behaviour that customers are well used to and give users the confidence of the results given by their spreadsheet.

Below are some examples of how OAK 5 helps reduce errors linked to dynamic arrays.

The workbook summary lists spilling formulae.

OAK 5 tests each cell in a formula listing to see whether the results of dynamic arrays are spilling out of it.

Consider the following example:

Apparently, rows 9 and 10 both contain formulae returning the same results. But a closer inspection reveals that while the formula on row 9 is copied across, row 10 contains a unique formula in D10.

OAK previous versions like OAK 4, will identify the formulae on row 9 but only one formula on row 10 while OAK 5, our latest version, recognises the dynamic array formula on row 10 and the spilled formulas on the right:

OAK 4 report:

OAK 5 report:

With OAK5, you can easily identify worksheets containing dynamic arrays and therefore focus your analysis in those particular areas using some of the other traditional OAK reporting tools.

The ‘distinct formulas’ and ‘risk analysis’ lists formulae that manipulate dynamic arrays.

OAK recognises the operators @ and #, which Microsoft has introduced to help handle dynamic arrays. It identifies formulae that use those operators, allowing you to scrutinise them.

With OAK 4:

With OAK 5:

The same behaviour occurs on the Risk Analysis report, where OAK 4 does not recognize dynamic array formulas or the ‘@’ and ‘#’ operators while OAK 5 is able to identify the dynamic array formulas and count the number of spilling formulas as a risk factor:

Risk Analysis with OAK4:

Risk Analysis with OAK5:

The map tool shows spill regions.

As already noted, cells are no longer either empty or occupied. They can now be somewhere between the two, lacking any content but showing values spilled out of other cells.

In OAK5, the maps show these areas like normally occupied cells using a slightly different shade (blue in the screenshot below) to signal their intermediate status.

With OAK 4:

With OAK 5:

The compare tool reports where workbooks differ in spilled values

OAK5 makes sensible reports of the differences between worksheets that relate to dynamic arrays spilling differently.

A formula comparison between the two worksheets below would return completely different results when using OAK 4 and OAK 5:

Worksheet 1:

Worksheet 2:

Results when comparing with OAK 4 – note that the spilling formulas on row 7 are not recognized as a change:

With OAK 5, the dynamic array formulas and corresponding spilled formulas are recognized by OAK:

Conclusion

Dynamic arrays can be very handy. Whether you use them or not, the point stands that spilling can happen unexpectedly, unwantedly, and undetected in your spreadsheet which is something you should be mindful of.

The potential for output to change if the model inputs are altered from one run of a model to the next has added complexity to the review of a spreadsheet.  It is essential for anyone making decisions based on the output of a model, to have certainty the numbers are right, and therefore that dynamic arrays are behaving as intended.

By helping you to consistently and efficiently identify and localise dynamic arrays, OAK brings confidence that the numbers can be trusted.

Get in touch now to upgrade to OAK5