Forum Discussion
Spill error for valid formula
I updated from Office 2010 to Office 2021 some months ago, and have had a number of problems with a income tax workbook giving errors to formulas that have worked for years under 2010 and earlier Office versions. The current problem is the #SPILL! error for some cells. An example is this formula: =AND(NOT( UseSchD.WS),MAX(QualDividends,CapGain,AND('Schedule D'!L57>0,'Schedule D'!M67>0))>0). Using T for TRUE and F for False, Excel's formula evaluator, fx, gives T for NOT, a numerical value for MAX, FFF for second AND conditions and its result, and T for the formula overall. I don't understand why the formula evaluator generates a valid result but Excel 2021 flags the cell as in error.
2 Replies
- #SPILL! indicates Excel is evaluating the formula as an array and the result is colliding with a value in the Spill region. To force Excel to NOT evaluate it as an array, we can insert the "Implicit intersection operation": @. I can't tell you where in your formula it needs to go (sorry) but this link provides an explanation of what changed and how to adapt to the new calculation engine. https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34#:~:text=Implicit%20intersection%20logic%20reduces%20many%20values%20to%20a,in%20the%20background%29.%20The%20logic%20works%20as%20follows%3A
I finally tracked down the origin cell responsible for the spill error displayed for dozens of dependent cells, and it was trivially simple (=MIN(I35,I39)), where both operands were numeric and error-free. As for other similar problems (#VALUE!), the problem can be resolved by copying the text in the problem cell and immediately pasting it back. But the workbook is still unstable, and problems occur when unrelated changes are made, maybe unimportant worksheet row deletions or insertions?
Thanks for your help, I'll try the @ operator the next time Excel presents me with one of these undeserved errors.
