Give the named function argument a default value if not specified

Question

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.

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

Nadeau

Answer 1

This is called optional-arguments and it's a well known feature in major programming languages like javascript and python. It is also available in built in functions. But unfortunately, it's not currently available in google-sheets. Microsoft introduced LAMBDA a year back in excel. 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.

Silicle

Answer 2

With a work-around you can get close to your desired solution, in some cases, by providing fallback values via IF(ISBLANK()) in your named function.

This will only work if your name function has more than 1 argument (otherwise Sheets will return an error).

Replace your variables, ex:

sort_column

With a check if they are blank, and a default value to return if they are blank:

IF(ISBLANK(sort_column),DEFAULT_VALUE,sort_column)

In this case the resulting function is:

=SORT(FILTER(range,range>0),IF(ISBLANK(sort_column),1,sort_column),IF(ISBLANK(is_ascending),1,is_ascending))

Use the function with:

=SORT_POSITIVE(A2:A,,)

I also use this for optional validation, error checks, etc. which can be enabled via arguments while prototyping.

Copy the updated Sheets file

Hand