Dynamic Arrays and #SPILL! errorsby
In the previous article in this series, we looked at the basics of the new Dynamic Arrays.
Before I carry on there is a question I do need to cover with my far too simple example: what happens if something gets in the way of a Dynamic Array?
In this example, in cell G40, I have typed in the obtrusive text, “I’m in the way”. And it quite literally is. Consequently, I have generated the brand new #SPILL! error. The formula cannot spill, so the error message is generated accordingly.
#SPILL! errors are returned when a formula returns multiple results, and Excel cannot return the results to the spreadsheet. There are various reasons an #SPILL! error could occur:
Spill range is not blank
As in my example (above), this error occurs when one or more cells in the designated spill range are not blank and thus may not be populated.
When the formula is selected, a dashed border will indicate the intended spill range. You may select the error “floatie” (believe it or not, this is what Microsoft call these things!), and choose the ‘Select Obstructing Cell’ option to immediately go the obstructing cell. You can then clear the error by either deleting or moving the obstructing cell's entry. As soon as the obstruction is cleared, the array formula will spill as intended
The range is volatile in size
This means the size is not “set” and can vary. Excel was unable to determine the size of the spilled array because it's volatile and resizes between calculation passes. For example, the new function SEQUENCE(x) (explained in detail below) generates a list of x numbers increasing by 1 from 1 to x. That’s fine, but the following formula will trigger this #SPILL! error:
Dynamic array resizes may trigger additional calculation passes to ensure the spreadsheet is fully calculated. If the size of the array continues to change during these additional passes and does not stabilise, Excel will resolve the dynamic array as #SPILL!
This error type is generally associated with the use of RAND, RANDARRAY and RANDBETWEEN functions. Other volatile functions such as OFFSET, INDIRECT and TODAY do not return different values on every calculation pass so tend not to generate this error
Extends beyond the worksheet’s edge
In this situation, the spilled array formula you are attempting to enter will extend beyond the worksheet's range. You should try again with a smaller range or array. For example, moving the following formula to cell A1 will resolve the error, and the formula will spill correctly
As I will explain shortly, Tables and Dynamic Arrays are not yet best friends. Spilled array formulae aren't supported in Excel Tables (generated by CTRL + T). Try moving your formula out of the Table, or go to Table Tools -> Convert to range
Out of memory
I have forgotten what this one means. Sorry, I couldn’t resist that. The spilled array formula you are attempting to enter has caused Excel to run out of memory. You should try referencing a smaller array or range
Spill into merged cells
Spilled array formulae cannot spill into merged cells. You will need to un-merge the cells in question or else move the formula to another range that doesn't intersect with merged cells.
When the formula is selected, a dashed border will indicate the intended spill range. You can again select that wonderfully named error floatie and choose the ‘Select Obstructing Cell’ option to immediately go the obstructing cell. As soon as the merged cells are cleared, the array formula will spill as intended.
Unrecognised / fallback error
The “catch all” variant. Excel doesn't recognise, or cannot reconcile, the cause of this error. Here, you should make sure your formula contains all of the required arguments for your scenario.
Implicit Intersection Implications
It may be an alliteration and sound like something you can get arrested for, but Dynamic Arrays do come at a price. There aren’t many users out there who used them, but there are some – and hence there will be some legacy calculations affected.
In the past, if you entered =A$1:A$10 anywhere in rows 1 through 10, the formula would return only the value from that row. In fact, a spreadsheet our company is presently auditing relies on this behaviour. However, in the brave new world of Office 365 (albeit selected Insider recipients for the time being), typing this formula would create a Spilled Array Formula. To protect existing formulae, we need a new – if not instantly breathtaking – function.
Don’t judge the upcoming functions on our first newbie. This one is essential to keep Excel running smoothly, but it’s probably safe to say it won’t set the world alight.
The @ operator returns a single value using logic known as implicit intersection. @ may return a value, single cell range or an error.
When the supplied argument is a range, @ will return the cell at the intersection of the row or column of the formula cell. Where there is no intersection, or more than one cell falls in the intersection, then @ will return a #VALUE! error. When the supplied argument is an array, @ returns the first item (Row 1, Column 1).
In the example below, the two @ formulae are supplied a range, H13:H27, and return the values in cells H17 and H22 respectively.
I can see an argument going forward that some form of OFFSET (e.g. “NEXT” or “PRIOR”) may be needed in due course – but no one is expecting everything to come together on Day 1. Think of @ as the necessary but unsexy operator that allows old formulae to continue working as they did previously.
Liam Bastick is author of an Introduction to Financial Modelling, which provides a simple walkthrough of the common perils and pitfalls of financial modelling. Presently, this book is only available on Amazon Australia but if anyone would like to buy a copy directly contact Liam via this link. AccountingWEB readers receive a 10% discount plus p&p (including tracking and insurance at cost).
You might also be interested in
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...