If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Warning when in another cell after entering a formula.
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 |
#2
|
|||
|
|||
Warning when in another cell after entering a formula.
"Ephraim" wrote:
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 ----- "Ephraim" wrote in message ... 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 |
#3
|
|||
|
|||
Warning when in another cell after entering a formula.
PS, unrelated to your question....
"Ephraim" wrote: 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 ----- "Ephraim" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|