Forum Discussion
Errors / Omissions in All Functions Listing
Perhaps, these tests will explain the complexity of a blank cell, an empty text, and the IF arguments:
From the foregoing, we can infer these conclusions:
1. Empty text is not, but is counted as, blank;
2. Empty text is a text that has zero number of characters;
3. When the value for the logical_test argument of IF is omitted, it is presumed as evaluating to FALSE or equal to 0;
4. When the value for the value_if_true argument of IF is omitted, it is presumed as 0;
5. When the value for the value_if_false argument of IF is omitted, it is presumed as 0; and
6. When the value_if_false argument of IF is omitted, it is presumed as FALSE.
I think we agree!
There is a Microsoft confusion on the meaning of 'blank'
ISBLANK returns TRUE or FALSE if a cell is empty
COUNTBLANK counts the cells that are 'blank' but does NOT use the same definition of 'blank' that is used by ISBLANK.
To count empty cells we must use SUMPRODUCT to sum the returns of coerced ISBLANK formula.
Much nicer would be my suggestion for two functions:
ISEMPTY - Same as ISBLANK but appropriately named
COUNTEMPTY - Counts number of empty cells rather than having to workaround problem.
As for the IF function: Can we return to the sanity of having 'value-if-true' a compulsory argument like it always was rather than the insane and dangerous return of 0.
But Microsoft doesn't pay attention to any of this, so we're just wasting our time. Patent errors in the All Functions listing remain there over a month after I pointed them all out. They don't give any attention at all to the problems of users. Those in charge of the All Functions list and important resources that feed off that list are plainly incompetent.
Search our John Cleese interviews at Cornell University and you'll see that he is speaking 100% the truth when it come to those guys responsible for providing help to Excel users.
Regards
Regards
The value_if_true argument of IF is required but the value of that argument is optional.
Let me explain through these discussions:
1. In I3, only the value of the value_if_false argument was omitted, which is presumed as 0; the value_if_false argument was not omitted, as proved by the presence of the second ,;
2. In I4, the value_if_false argument was omitted, which is presumed as FALSE;
3. In I5, the value of the value_if_true argument was omitted, which is presumed as 0; the value_if_false argument was omitted, which is presumed as FALSE;
4. In I6, the value of the value_if_true argument was omitted, which is presumed as 0; and
5 In I7, both the values of the value_if_true and the value_if_false arguments were omitted, which are presumed as 0.
Succinctly, omission of the value of an argument is distinct from the omission of an argument!
To summarize, the value_if_true argument of IF is required; the value_if_false argument thereof is optional. Whenever the value of an argument in IF is omitted, it is presumed as 0.
IF(1,) actually returns a missing argument which is treated as a blank in many formulas, for example the following all return TRUE:
=ISBLANK(IF(1,)) =IF(1,)="" =IF(1,)=0 =IF(1,)=FALSESince blank cells cannot contain formulas in Excel =IF(1,) gets converted to zero in cell results. Google sheets on the other hand returns a blank value which is perhaps a more logical approach.
- Interesting what you say about Google sheets being more logical.
However, it seems to me that if you were so minded as to leave the value_if_true argument optional, you would give it the return of TRUE following the same logic that always applied if the value_if_false was left optional. In that case the underlying values of the opitional arguments would be 1 for value_if_true and 0 for value_if_false.
But, I have yet to find anyone who mounts any argument for supporting the first step of the process which is, "Should the value_if_true argument be optional?" If that is supportable, we can then move on to the second step which is to ask, "What should the default value be?"
What you are working towards is the vital area of trapping the problem that arises because Microsoft have failed to approach the problem logically.
My vote goes for rejuecting the first proposition and leaving what was working very well alone.
Thanks for your thoughts!
Norman
Twifoo I would argue that this default of 0 should not be the case. Why was it changed? It was perfectly satisfactory in Excel since 1985 and in other spreadsheet programs all through to the unannounced change. All user inputs need validating, how do I validate for a cash flow a conscious return of 0 and a return of 0 by default.
I note that you accept the view on the idiosyncrasies of definitions of ISBLANK and COUNTBLANKS.
Meanwhile, Microsoft leaves patent errors in the All Functions Listing, the User Interface, and in other crucial user resources unaddressed despite them having been pointed out for months.
See John Cleese's views in the Cornell University interview by Cornell University Press Director Dean Smith (9/11/17) and the shorter John Cleese Rose d'Or Lifetime Achievement Speech after around 12:00 minutes. You'll see he and Dunning of Cornell are right.
