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.

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

"Dave Peterson" wrote...
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.

This isn't a short-circuit evaluation, at least not as the term is commonly
used in programming. Common usage means that boolean evaluation stops when
the final result is certain. For instance, in

AND(a=b,c=d)

if in fact a < b, there's no need to evaluate c=d since the result of AND
would be false with certainty. On the other hand,

OR(a=b,c=d)

if in fact a = b, again there's no need to evaluate c=d since the result of
OR would be true with certainty. Also, if Excel's IF didn't work this way,
there'd be no way to use hard-coded error values in formulas like

=IF(a=b,c,#N/A)

or even

=IF(X,Y,#REF!)

It's not all that difficult to find such #REF! errors, even in HUGE
workbooks. Just use Edit Find to search for cells with formulas containing
#REF!.

There's a deeper point. #REF!A1 is a syntax error, and can't be entered in a
cell formula anywhere. Should Excel deal with events that generate such
syntax errors in the same way it does when they're entered? For example, if
there were 10,000 cells with formulas referring to cells in worksheet X, and
X were deleted, should Excel then iterate through all the 10,000 cells
forcing the user to resolve the syntax error on a cell-by-cell basis? Even
at no more than 5 seconds average time to fix this in each cell, that would
take almost 14 straight hours to fix.

On a tangent, external references are only valid when the referenced files
exist. What should happen when they don't? For that matter, if the active
workbook doesn't contain a worksheet named foo, when I enter a formula like

=IF(a=b,"foo",foo!A1)

Excel displays a File Not Found dialog implicitly prompting me to select the
file. If I press [Esc] or click on the Cancel button *and* a < b, then
Excel evaluates this as #REF!. However, once a = b, Excel evaluates this as
"foo". This is a much nastier potential bug than #REF!A1.

The Excel developers were and remain free to specify Excel's behavior when
syntax errors are caused by events other than entry of formulas into cells.