Dynamic arrays can spill. The means a single formula populates a range. Most Excel functions can refer to and use a spill range. This causes the formulas to spill to match the spill range. There are some functions that don’t work with spill ranges. Here is a technique that forces a function to work with a spill range.

In the image below you can see that the N function does not spill down to match the spill range. The formula in cell A6 creates a spill range that’s spills down the column.

The N function converts text to zero and leaves numbers unchanged. I have covered it in the blog posts below.

Treating text as zero in Excel

The Magical N Function in Excel

By referencing A6# the N function should spill down the column to match the spill range, but it doesn’t.

To force it to spill down to match the spill range you can use the BYROW function.

The BYROW function splits up a vertical spill range into separate rows to allow you to process them. Each row is passed to a variable in a LAMBDA function (I have used x as the variable) to enable a calculation.

The required formula is.

=BYROW(A6#,LAMBDA(x,N(x)))

There is also a BYCOL function if you need to work with a horizontal spill range. It splits a horizontal spill range into separate columns.