Apr 09 2021 10:01 PM
Apr 09 2021 10:01 PM
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.
May 05 2021 02:58 AM
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.
May 05 2021 03:39 AM
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.
May 05 2021 09:49 AM - edited May 05 2021 09:51 AM
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.
May 05 2021 11:46 AM
@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.
May 06 2021 12:14 AM
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,)=FALSE
Since 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.
May 06 2021 03:18 AM
@Sergei Baklan Hi Sergei! I suspect that Ron de Bruin and I made this request 20 years ago but can't track it down. I know that the idiosyncratic pair of definitions was mentioned in our early 2000s VBA version of the excelfunctionbible.
But what's 20 years? We've waited since July 2004 for Microsoft to give us a function for Easter despite publishing both VBA and Formulas for Easter in both Gregorian and Julian 'flavours' for 1900 to 9999 covering both ordinary and 1904 date settings.
That's even more important to users because of its utility for determining the plethora of Easter based holidays in the WORKDAY.ITNL and NETWORKDAYS.ITNL functions.
I seem to recall that the only new function that the old MVPs wanted that Microsoft ever provided was FORMULATEXT. However, the new functions in Excel365 are really superb so I'll still be recommending Excel until the cows come home.
Keep up the great job Sergei, there's some who really appreciate it.
May 06 2021 03:34 AM
May 06 2021 04:30 AM
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!
May 06 2021 09:20 AM
Perhaps it will be some compromise to work with blank as a value in concept of dynamic arrays and lambdas without extend on traditional functions. At least right now we may check blanks like =XMATCH(,range). Some more steps in this direction.
May 07 2021 01:24 AM
Wasn't aware of XLOOKUP / XMATCH blank inputs, that's good to know and is a step in the right direction. Dynamic arrays also allow one to simulate a blank return using an extra row or column, which has its uses eg
entered in B1 leaves C1 blank if A1 is zero or blank; otherwise C1 returns the value in A1.
May 07 2021 04:43 AM
May 07 2021 06:10 AM
I see, thank you. Yes, that could work.
As for the Power Query in general, my position if something could be done by formulas it's better to with them. Power Query and PivotTable require refresh, that's the main limitation. However, they are quite powerful even if Excel has "limited edition" of data modelling tools (e.g. compare PQ Excel and PQ Online, etc).