Examples of formulas that return the #spill error
Most of the time, you’d see the #SPILL error for array functions. These functions result in an array of cells.
And if there are not enough empty neighboring cells to contain the results, you will end up with a #SPILL error in the formula cell.
You might turn a simple formula into an array formula too. Array formulas are those that give multiple results. These results may spill across the neighboring cells (down or to the right).
Here are a few examples of such functions and formulas:
Example No. 1: RANDARRAY function:
The RANDARRAY function is a dynamic array function.
It stands for a Random Array – and you guessed it right. It produces an array of random numbers🚴♀️
So, say we need an array of random numbers that is 5 rows tall and 4 columns wide.
Let’s write a RANDARRAY function in Cell A1 that gives us 5 rows tall and 4 columns wide array.
= RANDARRAY (5,4,0,100)
The first and second arguments are for the required number of rows and columns. The third and fourth arguments are for the minimum and maximum values that we need in our array.
Note that the 4th row and the 3rd column in the image above already contain values 👀
And here are the results.
We only have a #SPILL error!
That’s because the resulting range had to spread across the 3 columns neighboring Cell A1 (Columns A to D). And 4 rows neighboring Cell A1 (that makes Rows 1 to 5).
But in our example, Row 4 and Column C already had data.
As all the required neighboring cells to populate the results were not empty – the RANDARRAY function returns the #SPILL error.
Now let’s clear out all the cells and write it again 🧹
This time the results are widespread.
Example No. 2: POWER Function
The POWER function is not a dynamic array function. It simply raises a number to a given power and returns the answer.
The same formula that we used in old-school math 👶
2 raised to the power 2 is 4.
Let’s do it here:
We have a list of numbers and we want to raise them all to power 2.
Let’s write the POWER function for all of them at once:
= POWER (A2:A5, 2)
The cell range A2:A5 contains all the numbers. Whereas “2” is the power to which these numbers must be raised.
Don’t forget to note that the list for powers is not empty. It already has a number in the third row.
The POWER function fails to return the results as the spill range is not vacant 👆
Vacate Cell B4 and reapply the formula:
And this time, we get the POWER of all the numbers in one go.