I was working on an answer to the following question: https://stackoverflow.com/a/77547442/12634230 when I got a formula that returned the correct results, but when any value was changed within the range, the result would be off. When we reentered the formula, the calculation would be correct again.
I did some searching and found out what made Excel behave this way; it was the following the TOCOL filtering out the values that are non-numeric and/or blank.
Let say we have the following data:
| A | B | ||
|---|---|---|---|
| 1 | 1 | 1 | |
| 2 | 2 | 2 | B2 formulatext =A2 |
| 3 | 0 | 0 | |
| 4 | 3 | 3 | A4 formulatext =SUM(A1:A3) |
In C1 we use: =TOCOL(A1:A4/ISNUMBER(A1:A4),3)
In D1 we use: =TOCOL(B1:B4/ISNUMBER(B1:B4),3)
If we now change any of the values in the sum range A1:A3, A4 - the cell containing the sum - will be excluded from the TOCOL-result (C1#).
If the change would be in A2, both the TOCOL-results C1# & D1# will be affected, since it will trigger a recalculation of A4 and B2 (both referring to the changed cell), these cell values will be excluded from the spill results and what surprised me, triggering a (re)calculation of the workbook (F9) does not fix the wrong spill results.
Only if we click on the formula cell, don't change the formula, but hit enter; it will calculate to the correct/expected result.
It seems that the TOCOL works faster than the calculation results, or is only triggered to check the directly changed cell, instead of the cells within the range that are indirectly changed as well.
It works well with argument 2 (ignore errors), but 3 (ignore blanks/errors) results in this behaviour.
Does somebody have a better/official explanation to what I encountered?
Would that mean that TOCOL should not be used if the range contains formula-cells?
FILTER seems to show the correct results regardless, but this kind of scares me for using TOCOL.
I'm using Office 365 (Insider Beta Chanel).