NESTED IF Formula not returning value despite conditions being true...

[mosmanpk]

Calling All Excel Gurus!

I need your expertise to advise me on why this nested if formula isn't returning the $20K value despite all conditions being true.

See screenshots below for details

Thanks in advance!

[hrlngrv]

Screenshots are useless compared to attaching sample files. Even with proprietary data, you could replace all text with Xs and clear all numbers other than those producing the problem.

In this case, I'd guess that the problem is floating point rounding error. Instead of using SUM(...)=0, change that to ABS(SUM(...))<1E-6.

[joeu2004]

advise me on why this nested if formula isn't returning the $20K value despite all conditions being true.

As someone else wrote: if a picture is worth 1000 words, an Excel file is worth 1000 pictures. It is better to attach an example Excel file (redacted and simplified) that demonstrates the problem.

That said....

It appears that you have a circular reference. In H10, the IF-expression references SUM($F10:H10).

It is unclear whether that is a typo (other parts of the IF-expression reference G10) or on purpose.

If you are not getting a "circular reference" warning (lower-left of the status bar), you have Iterative calculation mode enabled. IMHO, that mode is "evil"; many purposeful circular references cause "inexplicable" results.

Bottom line: fix the reference to H10; be sure that you do not depend on circular references on purpose; and be sure that Iterative calculation mode is disabled (click Files > Options > Formulas).

-----

Aside....

It is prudent to explicitly round calculations with decimal fractions that we expect to accurate to some number of decimal places. For example, ROUND(SUM($F10:G10),2).

It might be prudent to explicitly round other calculations, as well. For example, the cells that appear to be 0.00 due to cell formatting might be as large as 0.00499999999999999.

(But only if those values are the result of calculations. Normally, constants do not need to be rounded.)

Normally, cell formatting only affects the appearance of values, not their actual value.

-----

PS.... Unless you might have purposeful Excel errors in some referenced cells (they should be avoided, IMHO), consider the following form of the formula in H10 (fixing the circular reference), which minimizes function nesting.

Please Login or Register  to view this content.