Lambda Optional Argument

Question

I wanted to create a custom function called Pivot, that will take Range to be grouped and values to be summed up.

=LAMBDA(GroupBy1,GroupBy2,SumRange,

LET(Group,UNIQUE(HSTACK(GroupBy1,GroupBy2)),

HSTACK(Group,SUMIFS(SumRange,GroupBy1,TAKE(Group,,1),GroupBy2,TAKE(Group,,-1)))))

The function is doing fine but when I tried to make GroupBy2 optional using square brackets,..

=LAMBDA(GroupBy1,[GroupBy2],SumRange,

LET(Group,UNIQUE(HSTACK(GroupBy1,GroupBy2)),

HSTACK(Group,SUMIFS(SumRange,GroupBy1,TAKE(Group,,1),GroupBy2,TAKE(Group,,-1)))))

I am getting error: "You have entered too few arguments". Anything that should be fixed in the function?

Answer 1

Hi Everyone,

Seems like all optional arguments should be mentioned after compulsory arguments. So,

=LAMBDA(SumRange,GroupBy1,[GroupBy2],

LET(Group,IF(ISOMITTED(GroupBy2),UNIQUE(GroupBy1),UNIQUE(HSTACK(GroupBy1,GroupBy2))),

IF(ISOMITTED(GroupBy2),

HSTACK(Group,SUMIFS(SumRange,GroupBy1,Group)),

HSTACK(Group,SUMIFS(SumRange,GroupBy1,TAKE(Group,,1),GroupBy2,TAKE(Group,,-1))))))

is not giving "You have entered too few arguments" error.

Answer 2

Hi,

If your going to have optional parameters you need to use the IsOmitted() function.

here is one example from the web:

=LAMBDA(p1, [p2], IF(ISOMITTED(p2), p1, p2))

Answer 3

Hi Shane,

Thanks for pointing out for data.

If we have data like this:

H1	H2	H3
A	X	10
A	Y	20
B	X	10
B	X	30
B	Y	40

Now, specfying H3 as sumrange, H1 and H2 as groupby columns, the desired result would be:

H1	H2	H3
A	X	10
A	Y	20
B	X	40
B	Y	40

But whe we don't specify H2 as groupby2 the result should be:

H1	H3
A	30
B	80

OR if we specify H2 as only groupby column instead of H1:

H2	H3
X	50
Y	60

I think we just have to specify optional arguments at the end, that solved my issue.

Answer 4

Hi,

It might help if you could provide some dummy data, on the other hand it might not. The IFOMITTED seemed like a reasonable place to start checking. But we can't test your function without data.

Hopefully someone will see the issue.

Answer 5

Hi Shane,

Thanks for your reply.

If we try,

=LAMBDA(x,[y],[z],(x+y)*z)(2,,3)

It is working. And,

If we try,

=LAMBDA(x,[y],[z],Sum(x,y)*z)(2,,3)

this is also working without Isomitted function. Is it the case that some built-in functions can support optional arguments without Isomitted but others need to have it?