Forum Discussion
Errors / Omissions in All Functions Listing
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 Hope blank as a value will appear in Excel one day as well, that's quite old request and it wasn't refused.
I think the request for a blank value got pushed back since it may not be possible to introduce in a way that is fully compatibile with prior versions but for me the benefits of a null return still outweigh these concerns.
I take your point about returning TRUE for an omitted argument. The value_if_true argument is not truly optional in the sense that it can be fully omitted though. I think the confusion in function help/wizard may arise as there was also an IF function in the XLM language that only took one argument and was used in conjunction with an END.IF function - indeed I just checked that the IF(1) syntax still works on macro sheets.
In defence of the IF function I'd just like to add that it is probably the most versatile/powerful Excel function precisely because of it's simplicity - it just passes through whichever argument is chosen to return unchanged. So in addition to the basic scalars, range references and array values it can also return blank and missing values and now data types and lambda functions as well!
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.
