Forum Discussion
Errors / Omissions in All Functions Listing
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.
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!
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.
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.
SergeiBaklan 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.
