New Contributor

# Formula Spill

I have a formula that keeps on spilling to the remainder of the excel sheet but I don't want it to do that. How can I have the formula stop being a spill formula?

5 Replies

# Re: Formula Spill

This is what my formula looks like right now
=IF(\$F\$2:\$F\$2024=\$T\$4,LARGE(IF(\$M\$2:\$M\$95=\$S\$2,\$I\$2:\$I\$95),\$Q\$4:\$Q\$8),0)

# Re: Formula Spill

@jlopez31 When I enter that formula in an empty sheet I just get several NUM errors and mostly N/A as you didn't provide the context in which you use the formula. What's in those reference you use?

So, difficult to say without the file on hand, but try this, and copy down as far as needed.

``=IF(\$F\$2=\$T\$4,LARGE(IF(\$M\$2:\$M\$95=\$S\$2,\$I\$2:\$I\$95),\$Q\$4),0)``

Otherwise, you can perhaps share a file via Onedrive or something similar.

# Re: Formula Spill

I can share it via onedrive that way you can see what data i'm looking at and what I'm trying to pull

# Re: Formula Spill

Whenever you gave legacy Excel an array where it was expecting a value, it cut the array down to a single value that happened to be on the same row using 'implicit intersection'.  That can be emulated in modern Excel but preceding the array with an '@' as @Riny_van_Eekelen suggested.  That is not usually the best solution though, it is better to write a formula to take advantage of array behaviour.

With the formula you provided, I suspect the initial check of over 2000 values is serving very little purpose  Your formula is capable of returning 5 meaningful values and the purpose of having a further 2000 zeros or #N/A is not clear.