I have no real insight to the real reason, but I would bet that the excel
developers thought that they could have excel calculate quicker by
short-circuiting this kind of formula.

Markthepain wrote:

In a formula using multiple IF statements an error created by a #ref does not
show in the calculation result as a #ref error. For example, with A1=1 and
B1=2
=IF(A1<B1,0,IF(tab1!A1=B2,1,2)) evaluates correctly to 0 when Tab1 exists,
If Tab1 is deleted, the formula evaulates to
=IF(A1<B1,0,IF(#REF!A1=B2,1,2)). The user does not know that the formula now
has an error condition that will evaluate to #REF if the second condition
becomes true.

In large, complicated spreadsheets this situation allows for errors to
appear unexpectedly. My question is: why when changes or deletions are made
to calculation attributes isn't the entire formula scanned and errors
displayed when the recalculation is completed. The formula attributes are
scanned when the formula is created or changed.

--

Dave Peterson