in Excel is it possible to use a standard function in place of a lambda?

Question

When making templates in excel with indeterminate table sizes I like to use array functions

one fairly simple task I like to implement is row/column total so SUM seems like an appropriate tool, but SUM is a greedy function and will consume an array of any shape and collapse it into a cell.

Lambda functions to the rescue, BYROW sounds like it can do what I want, however defining the LAMBDA every time or using name manager feels awkward, especially when it is to alias a predefined function

using BYROW / BYCOL:

CODE A:
=BYROW (myArray, LAMBDA (row, SUM (Row))

and if I use Name Manager, I can simplify the statement to

CODE B:
mySum :=  LAMBDA (row, SUM (Row))
=BYROW (myArray, mySum)

However when I try (below) I get a #NAME error.

CODE C:
=BYROW (myArray, SUM) 

Is there a way to tell excel to check the default functions e.g. or is this the best options that I have is to use Name Manager

as an aside: Is Code A the best way for single use, and Code B optimal for reuse in workbook, or is there a simpler implementation that can be used to achieve the row/column subtotal.

Answer 1

It's not possible yet (March 2024), but it will be soon!

Check out this Microsoft Blog post about upcoming GROUPBY and PIVOTBY functions (currently in beta versions).

As part of implementing these functions, they've discuss Functions as Arguments - basically using LAMBDA functions. Many existing functions like SUM, AVERAGE, MAX and MIN can simply be selected from a list:

Plus they've added this functionality to all the existing functions that currently use LAMBDAs!

I'm very excited to see this roll out to general availability. But in the meantime, your approach of defining a simple lambda for sum, (and count, max, min...) is probably the best way to go. I've gone with an "L." prefix (for lambda) myself:

L.Sum    = LAMBDA(x, sum(x));
L.Max    = LAMBDA(x, max(x));
L.Min    = LAMBDA(x, min(x));
L.Count  = LAMBDA(x, count(x));
L.CountA = LAMBDA(x, counta(x));

Function Currying

Lastly, there is an approach referred to as function 'currying', which I've read about (but not experienced in). It's where you have a function that returns a function as a result. So it would be possible to write just a single function that depending on the parameters you pass would be equivalent to a sum(x) or count(x) function, or even textjoin(",", x) or textjoin("|", x). While I don't have an implementation completed, you could imagine a large IFS block. Then you could use it like this:

=BYCOL(A2:F9, Summary("sum"))
=BYCOL(A2:F9, Summary("count"))
=BYCOL(A2:F9, Summary("max"))
=BYCOL(A2:F9, Summary("textjoin",","))
=BYCOL(A2:F9, Summary("textjoin","|"))

Now all you have to do is write the function...!

Summary = lambda(func,[optparam], 
  lambda(x,
    ifs(
      func="sum",sum(x),
      func="max",max(x),
      func="min",min(x),
      func="count",count(x),
      func="textjoin",textjoin(optparam,true,x),
      "else"="else",NA()
    )
  )
);

And it works!!!

This FlexYourData post may be the only reference online to function currying in Excel. Well, was - this may be the second. :-)