Excel dynamic arrays: Don’t let ‘spilled’ numbers ruin your model 28 Mar 2022
Does your decision-making and livelihood depend on robust and dependable spreadsheets? If so, you need to know about Excel dynamic arrays.
In this blog, we focus on the two main issues regarding Excel 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 Excel 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 don’t. However, this process isn’t perfect and can cause issues when different parties using the same spreadsheet (eg. a client and their advisor) are on various versions of Excel.
While you may expect to find some bugs in a model built in Excel 2019 and opened in an older version of the software, 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 array functions in Excel or not, you must check if the workbook you received contains some and identify their location to ensure 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 Excel 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 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 spreadsheet’s structure and identify and locate dynamic arrays and potential spilling. OAK 5 can assist in achieving this.
How OAK 5 helps you solve the Excel 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 working with arrays in Excel 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 gives users confidence in the results given by their spreadsheet.
Below are some examples of how OAK 5 helps reduce errors linked to Excel 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. However, a closer inspection reveals that while the formula on row 9 is copied across, row 10 contains a unique formula in D10.
Previous versions of OAK, such as 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 Excel dynamic array formulas or the ‘@’ and ‘#’ operators while OAK 5 can 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 OAK 5, the maps show these areas as 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
OAK 5 makes sensible reports of the differences between worksheets that relate to dynamic arrays spilling differently.
A formula comparison between the two worksheets shown 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 Excel dynamic array formulas and corresponding spilled formulas are recognized by OAK:
Conclusion
Excel 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, so be mindful.
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. 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 Excel dynamic arrays, OAK brings confidence that the numbers can be trusted.
