Forum Discussion
Errors / Omissions in All Functions Listing
I have completed my review of the All Functions listing at support.microsoft.com
In total, I have 349 reports of errors/omissions/inadequacies. Of the 492 functions in my records some 'star' more than once.
These include 9 descriptions that are totally wrong and should be corrected. Some of those descriptions are clearly cut and pasted descriptions of other functions. I find it amazing that the description of such basic functions as TRIM and COUNTBLANK should be wrong.
I extended my work to cover syntax for all functions and found there were 29 cases where the user interface and / or the support.microsoft.com Help was wrong - most often in declaring an argument was required when it is demonstrably option.
Nearly all of the statistical and compatibility descriptions of the distribution functions are glaringly inadequate in terms of not pointing our that there are two distributions returnable that are quite different from one another.
One finding of especial concern is the current IF function syntax where the 'value_if_true' argument is optional. If you don't include a value_if_true argument the IF function returns 0. I believe that to be a significant problem in financial and other forms of analysis.
In many instances, there are similarly named functions where the description does not help a user determine what the differences are thus making a choice an obstacle course.
3 Functions CEILING.PRECISE, FLOOR.PRECISE, and ISO.PRECISE have disappeared from the user interface although they still exist once you open the argument parentheses and are still featured in the support.microsoft.com All functions listing.
Our dear friend DATEDIF appears in the support.microsoft.com All functions listing and has a Help page. However, it does not appear in the User Interface.
I attach my complete file complete with summaries and reconciliation.
14 Replies
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.
