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.