google-sheetsgoogle-sheets-formulaoptional-parametersnamed-function

Give the named function argument a default value if not specified

I created a formula to sort positive values.

=SORT(FILTER(A2:A, A2:A>0),1,1)

And turnd it to a named function with this formula definition, and named it SORT_POSITIVE

=SORT(FILTER(range, range>0),sort_column,is_ascending)

To use it like this.

=SORT_POSITIVE(A2:A,1,1)

It retrieves the sort column from the sort function within, as well as whether the sort is ascending or not.

Is there a method to set the named function's is_ascending default value to "1" when I don't set it to "0" or "1" for desending or ascending in the named function?
To be used Like this

=SORT_POSITIVE(A2:A)

Make a copy of the example sheet and the named function.

enter image description here

Recap: If the named function parameter is not supplied, use the default value.

Solution

  • This is called and it's a well known feature in major programming languages like and . It is also available in built in functions. But unfortunately, it's not currently available in . Microsoft introduced LAMBDA a year back in . After some time, it also added LAMBDA improvements. To quote,

    LAMBDA now supports optional parameters. To make use of optional parameters, all you need to do is wrap the optional name in “[]”.

    For example:

    =LAMBDA(param1, [param2], IF(ISOMITTED(param2), param1, param2))

    This lambda will return the value of param1 if param2 is omitted and otherwise return the value of param2.

    For feature parity with excel, I would expect Google to follow suit. But, currently, both ISOMITTED and [](optional arguments) are not available.