Errors / Omissions in All Functions Listing

Brass Contributor

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

@Norman_Harker 

Perhaps, these tests will explain the complexity of a blank cell, an empty text, and the IF arguments: 

Twifoo_0-1620207249133.png

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

@Twifoo 

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

 

@Norman_Harker 

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.

 

 

 

@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.

 

@Twifoo @Norman_Harker 

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.

@lori_m 

Hope blank as a value will appear in Excel one day as well, that's quite old request and it wasn't refused.

@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. 

See https://groups.google.com/g/microsoft.public.excel.worksheet.functions/c/w23pbWVijwk/m/JV5WMGntAsUJ

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.

 

 

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

@Sergei Baklan 

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.

 

@Norman_Harker 

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!

@lori_m 

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. 

@Sergei Baklan 

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
=SEQUENCE(,IF(A1=0,1,2),0,A1)
entered in B1 leaves C1 blank if A1 is zero or blank; otherwise C1 returns the value in A1.

@lori_m 

Thanks for sharing. Not sure right now how to use on practice, will think about that. By the way, to check only blank and separate it from zero =SEQUENCE(,IFNA(XMATCH(,A1),2),0,A1)

Some use cases where one might need a blank rather than "" as a formula result include preparation of data for charts, sparklines, pivottables or databases. I suspect Power Query may be a better choice in general for data manipulation of this kind - though my level is insufficient to say

@lori_m 

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).