Warning when in another cell after entering a formula.

[Ephraim]

I find this rather confusing:

Cell M7
=IF(SUM(H7:L7)=0,"",SUM(H7:L7))

Cell M8
=IF(COUNTIF(H8:L8,"W")<1,"",COUNTIF(H8:L8,"W"))

Cell M9
=IF(SUM(H9:L9)=0,"",SUM(H9:L9))

When I enter the formula in M7 and M8 everything works as expected,
however when I enter the formula in M9 I get the diamond with the
exclamation indicating "Inconsistent Formula" to the left of Cell M8.
Can anyone explain why?
Thanks

 

[Joe User]

I get the diamond with the exclamation indicating
"Inconsistent Formula" to the left of Cell M8.
Can anyone explain why?

It's Excel's way of insulting our intelligience by suggesting that we are
incapable of knowing what we want to do. I find that Excel is wrong
99.9999999999999% of the time ;-).

I turn off those warning. In Excel 2003, go to the Error Checking tab under
Tools > Options, deselect (uncheck) "Enable background error checking" and
click on Reset Ignored Errors.

Don't even give it a second thought.


----- original message -----

 

[Joe User]

PS, unrelated to your question....

Cell M7
=IF(SUM(H7:L7)=0,"",SUM(H7:L7))

Cell M8
=IF(COUNTIF(H8:L8,"W")<1,"",COUNTIF(H8:L8,"W"))

Cell M9
=IF(SUM(H9:L9)=0,"",SUM(H9:L9))

If you truly want null strings for some purpose, that's fine. Otherwise,
you might want to consider the following simplifications:

M7: =SUM(H7:L7)
M8: =COUNTIF(H8:L8,"W")
M9: =SUM(H9:L9)

all with the Custom format "General;-General;;" without quotes.

Thus, the true value of those cells will be zero under some circumstances,
but they will appear blank. This usually simplifies references to M7, M8
and M9 in other cells.

Note: If you chose some format other than General, the Custom format above
can accommodate that, too. For example, Number with 2 decimal places and
1000-Separator becomes "#,##0.00;-#,##0.00;;" without quotes.


----- original message -----