Forum Discussion
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?
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.
jlopez31 How exactly does your formula look like? Having access to the relatively new dynamic array functions is great. But you may have to adjust your formula by using an @-sign in the right place to avoid spilling.
- 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)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.
