MAXIFS doesn't work as expected invoked inside MAP using names from LET

Question

Just for testing purpose, I have the following formula showing different scenarios at the same time via HSTACK:

=LET(item, "A", colA, A2:A7, colB, B2:B7, ux, UNIQUE(colA),
  noMapOut, MAXIFS(colB, colA, item),
  mapOut1, MAP(ux, LAMBDA(item, MAXIFS(B2:B7, A2:A7, item))),
  mapOut2, MAP(ux, LAMBDA(item, MAXIFS(colB, colA, item))),
  HSTACK(noMapOut, mapOut1, IFERROR(mapOut2, "#CALC!"))
)

and here is the output:

From the result:

noMapOut: Returns the max of the group A, as expected. This scenario uses the names defined in the LET function. It demonstrate MAXIFS can work with names representing ranges defined in the LET function, when it is not invoked inside MAP.
mapOut1: It works. This scenario doesn't use the names defined in LET
mapOut2: Returns #CALC! error (I encapsulated it with IFERROR because otherwise it returns #CALC! for the entire HSTACK output). This scenario uses the same ranges as in mapOut1, just referring to them as names defined in the LET function. This is my real scenario, the other ones are just for testing purpose and to be able to isolate the problem.

Therefore the issue seems to be a combination of factors. The unexpected error appears when 1) using MAXIFS inside 2) MAP and 3) names from LET function are used representing the ranges.

Is there any explanation for that? Am I doing something wrong or it is just another Excel bug?

Here is the input data:

Group	Values
A	10
A	20
B	10
B	5
C	30
C	20

Note: I am not looking for a workaround, just trying to understand unexpected result of mapOut2 scenario. The workaround to get the expected result can be mapOut1 scenario or just using FILTER instead of MAXIFS:

=LET(rng, A2:B7, colA, INDEX(rng,,1), colB, INDEX(rng,,2), colAUx, UNIQUE(colA),
  MAP(colAUx, LAMBDA(item, FILTER(colB, (colA=item)
    * (colB = MAX(FILTER(colB, colA=item))))
  ))
)

It returns the expected result:

20
10
30

score 1 · Accepted Answer · answered Nov 20 '22 at 19:54

1

One might consider it a bug or, given their documented limitations, one might just add this to the list of racon-function limitations, i.e. by substituting

MAX((colA=item)*colB)

for

MAXIFS(colB, colA, item)

the 3rd column of your formula will evaluate correctly.

Similar behaviour can also be seen with SUMIFS, further incriminating that family of functions, i.e.

LET(colA,A2:A7,colB,B2:B7,ux,UNIQUE(colA),MAP(ux,LAMBDA(item,SUMIFS(colB,colA,item))))

produces a #CALC error, whereas the non-racon version

LET(colA,A2:A7,colB,B2:B7,ux,UNIQUE(colA),MAP(ux,LAMBDA(item,SUM(colB*(colA=item)))))

does not.

answered Nov 20 '22 at 19:54

Spectral Instance

1,971
2
3
6

Thanks @SpectralInstance. The problem with this workaround: `MAX((colA=item)*colB)` is that it works **only** for positive numbers. For example if all `colB` values for `ColA` equal to `item` are negatives. It returns `0`. If you know upfront they are going to be all negative values, then you can use: `-MAX((colA=item)*(-colB))`, but if you cannot guarantee the sign of the numbers you cannot use it. Similar problem for `MIN` instead of `MAX` – David Leal Nov 20 '22 at 20:45
1

@DavidLeal I wasn't explicitly trying to provide a workaround (since you didn't want one) but I think you can employ `AGGREGATE` for this purpose (albeit a more verbose formula), i.e. `=LET(item, "A", colA, A2:A7, colB, B2:B7, ux, UNIQUE(colA), noMapOut, MAXIFS(colB, colA, item), mapOut1, MAP(ux, LAMBDA(item, MAXIFS(B2:B7, A2:A7, item))), mapOut2, MAP(ux, LAMBDA(item, AGGREGATE(14,6,(POWER(10,LOG10(colA=item))*colB),1))), HSTACK(noMapOut, mapOut1, IFERROR(mapOut2, "#CALC!")) )` – Spectral Instance Nov 20 '22 at 21:48

David Leal · Answer 2 · 2022-12-05T21:07:49.220

Just to have the entire picture of this problem. First, it is not tied to a specific RACON function nor to a specific array function such as MAP. The following question: Using name variable from LET produces #VALUE! inside MAP using SUM with the range defined as INDEX : INDEX [duplicate] even though the problem was found in a different scenario, it seems to have the same root cause. As @Jos@Woolley pointed out in his question:

It appears that range variables defined via LET external to the LAMBDA to which they are being passed are first 'evaluated' to their corresponding array.

Here would the approach using @JosWoolley's suggestion from the other question, defining the variables inside MAP function via an internal LET:

=LET(item, "A", colA, A2:A7, colB, B2:B7, ux, UNIQUE(colA),
  noMapOut, MAXIFS(colB, colA, item),
  mapOut1, MAP(ux, LAMBDA(item, MAXIFS(B2:B7, A2:A7, item))),
  mapOut2, MAP(ux, LAMBDA(item, LET(x, A2:A7, y, B2:B7, MAXIFS(y, x, item)))),
  HSTACK(noMapOut, mapOut1, IFERROR(mapOut2, "#CALC!"))
)

Another question: Can't pass a range to a lambda using Let and Makearray functions addresses the issue with MAKEARRAY function too.

I posted the question in Microsoft Tech Community: MAXIFS doesn't work inside MAP using names from LET and I got another workaround:

=LET(item, "A", colA, LAMBDA(A2:A7), colB, LAMBDA(B2:B7), ux, UNIQUE(colA()),
  noMapOut, MAXIFS(colB(), colA(), item),
  mapOut1, MAP(ux, LAMBDA(item, MAXIFS(B2:B7, A2:A7, item))),
  mapOut2, MAP(ux, LAMBDA(item, MAXIFS(colB(), colA(), item))),
  HSTACK(noMapOut, mapOut1, IFERROR(mapOut2, "#CALC!"))
)

so the idea here is to define the ranges via as-user LAMBDA functions:

colA, LAMBDA(A2:A7), 
colB, LAMBDA(B2:B7)

Then it can be used inside MAP.

MAXIFS doesn't work as expected invoked inside MAP using names from LET

2 Answers2

Linked