Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Warning when formula in a cell is changed | Excel Discussion (Misc queries) | |||
entering a formula into a cell with text | Excel Discussion (Misc queries) | |||
How to protect cell with formula from being entering info | Excel Worksheet Functions | |||
With a formula in a cell, what is the warning symbol for? | Excel Discussion (Misc queries) | |||
Warning while entering duplicate values in a cell | Excel Discussion (Misc queries) |