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

 

 

 

The main idea is to find the maximum value per unique group (ColA).

 

  • mapOut1: It works. This scenario doesn't use the names defined in LET
  • mapOut2: Returns #CALC!. This scenario uses the same ranges as in mapOut1, just referring to them as names defined in the LET function
  • 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.

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

 

The input data is as follow:

 

GroupValues
A10
A20
B10
B5
C30
C20

 

The output is as follow:

 

2020#CALC!
#N/A10#N/A
#N/A30#N/A

 

MAXIFS only works with ranges, therefore the names in LET are defined as ranges. 

 

Is there any  explanation for that or it is just another Excel bug?

 

Note: I am not looking for a workaround, just trying to understand unexpected result. The workaround to get the expected values can be as follow 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))))
  ))
)

 

 

 

attached the testing file for more details

 

Thanks,

 

David

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    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 for RACONS function 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).

       

      Here you can find more details about this approach.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Nov 21, 2022

    davidleal 

    This is fascinating because colA and colB are evaluating when I step through the MAP - Excel just won't calculate it.  The simplest explanation that comes to mind is Excel is reading colA/colB as full on arrays in the context of the LAMBDA.

     

    Naturally, it works when I write it as a legacy CSE MAX-IF

     

     

    =LET(item, "A", colA, A2:A7, colB, B2:B7, ux, UNIQUE(colA),MAP(ux, LAMBDA(item, MAX(IF(colA=item,colB)))))

     

     

  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 21, 2022

    davidleal This is interesting.  It appears it is taking the named ranges and trying to do some array function on then giving the array of array error.  I tried a few variation and found using the 'reduce' workaround produced an array of 18 (i.e. 3*6) and supports the array of array issue.  Still not sure why and even tried adding $$ to the ranges and creating new names as functions of the original with no difference.  By the way, the FILTER work around can be simply:

    MAP(ux, LAMBDA(item, MAX(FILTER(colB, colA=item,0))))

    I attached my sheet if you are curious of the different things I tried. 

Resources