Tricking Non-Dynamic Array Formulas into Spilling

Tricking non-dynamic array formulas into spilling isn’t something you’d naturally think of doing.

Dynamic Arrays are great but sometimes the formulas that refer to them behave in unexpected ways, and sometimes these don’t spill … without any obvious reason.

For example, the DATE function spills, but the EOMONTH function doesn’t. The AND, OR and NOT functions don’t spill, yet the formula =(A1>100)*(A2<50) does spill.

This can be very frustrating!

Dynamic Tricks

Some functions that don’t spill however can be “tricked” into spilling!

In the example below, the DATE function is spilling no problems, however, the EOMONTH function is currently showing the #VALUE! error and isn’t spilling. Grrrrr! Why doesn’t it behave like the DATE function???

If we modify our EOMONTH formula slightly, however, we can get our EOMONTH formula to also spill. All we need to do is add zero onto our spill cell and it starts spilling!

Bizarre, unexpected, stupid … I hear you say? Yes, I agree with you!

Even more bizarre is that all you actually need to do is put a plus in front of your spill cell and this will cause the formula to spill!

Why is it so?

Charles Williams, Excel MVP, provided this explanation:

Any time you add an operator to a function parameter Excel has to evaluate the resulting expression before passing it to the function.

So when you add the + to the C7# EOMONTH sees the result of evaluating +C7# – which is an array of values

Without the + EOMONTH sees a range reference.

In VBA UDF terms, think of the parameter being defined as a variant which can contain any type of data including an array of values or a range and so your VBA has to either check which type it actually got, or always convert using Param.Value2=Param.Value2 and then check if it got a scalar or an array.

So inside EOMONTH, there is code that detects whether the parameter has been passed an array of values or a range reference, and it looks like when it is passed a range, it does not expand it.

But not Always!

If you apply this technique to an AND function however it still refuses to spill:

The only way to get AND-like functionality (that I’m aware of!) is to use the method on row 11 above.

Conclusion

If none of this makes any sense to you, just remember to try putting a + in front of your spill cell reference next time you have a non-spilling formula and you might be pleasantly surprised … or not!

EndNote

After publishing this article, Peter Bartholomew also provided an example of using MMULT to achieve AND functionality (though it’s a bit complicated) and mentioned Charles Williams’ AND.COLS custom function (which is much simpler), available as part of his FastExcel SpeedTools add-in. So, if you’re looking for a simpler, more logical solution that won’t require multiplying spilled ranges together, take a look at Charles’ functions (note: there is a licensing cost for the add-in).