Forum Discussion

davidleal's avatar
davidleal
Iron Contributor
Nov 20, 2022
Solved

MAXIFS doesn't work inside MAP using names from LET

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, M...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 21, 2022

davidleal 

Explanation (dereferencing ranges) and some workaround (thunking) are given here https://www.linkedin.com/feed/update/urn:li:groupPost:12656547-6951207529683001345/?commentUrn=urn%3Ali%3Acomment%3A%28groupPost%3A12656547-6951207529683001345%2C6951232517186203648%29 by lori_m 

Using his suggestion that could be like

=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!"))
)

@ 

davidleal's avatar
davidleal
Iron Contributor
Dec 05, 2022

SergeiBaklan interesting approach and weird how this could work I would expect the Excel team fixes it, but it is not just happening on this scenario with MAP, I found it also with MAKEARRAY when it is required a range function. . The link you shared doesn't work. Please would you provide the correct one, I would like to know more about this problem

 

Here is another workaround and explanation about why this happens:

 

 

=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!"))
)

 

so forhttps://exceljet.net/articles/excels-racon-functions that requires a range and not an array. It seems that when the name is defined outside of MAP (or any other array function, it is a general problem) it is implicitly converted to an array, so it won't work for RACON functions. When the name is internal (i.e. defined inside MAP), then it works like in the above example (x, y).

 

https://stackoverflow.com/questions/74595902/using-name-variable-from-let-produces-value-inside-map-using-sum-with-the-rang you can find more details about this approach.

Resources