Forum Discussion

ecovonrein's avatar
ecovonrein
Iron Contributor
Mar 30, 2023
Solved

MAP x MAP

Can anyone tell me what is wrong with this formula? =MAP(C23:D23;C24:D24;LAMBDA(a;b;SUM(MAP(C5:G5;C6:G6;C7:G7;LAMBDA(c;d;e;1))))) I get #VALUE #VALUE. Thanks. PS: Works when you replace the SUM()...
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Mar 31, 2023

    ecovonrein 

    Why don't you post something closer to your actual problem?  I am sure we can help and so, save you some pain!  For example, YEARFRAC tends to play better if you put a + sign in front of multi-cell range references to convert them to arrays.  If you are trying to return the results in a 2D grid, MAP has an annoying flaw in that it will not return a nested array / array-of-arrays (i.e. the correct answer to your problem).  Workarounds include MAKEARRAY and REDUCE combined with VSTACK.

ecovonrein's avatar
ecovonrein
Iron Contributor
Mar 31, 2023

This has been my worst experience with Lambdas to date. The stable work-around seems to be:

=BYCOL(VSTACK(C23:D23;C24:D24);
LAMBDA(x;LET(
a;INDEX(x;1;1);
b;INDEX(x;2;1);
SUM(BYCOL(VSTACK(C5:G5;C6:G6;C7:G7);
LAMBDA(y;LET(
c;INDEX(y;1;1);
d;INDEX(y;2;1);
e;INDEX(y;3;1);
1
))
))
))
)

That is, I VSTACK the input ranges into BYCOL and then unpack the stack inside the LAMBDA.  Weird.

SergeiBaklan's avatar
SergeiBaklan
MVP
Mar 31, 2023

ecovonrein 

My guess

=SEQUENCE( ,COLUMNS(C23:D24), COLUMNS(C5:G7), 0 )

returns exactly the same result

  • ecovonrein's avatar
    ecovonrein
    Iron Contributor
    Mar 31, 2023

    Ha ha, Sergei. There must be a million ways to arrive at 5 5. ğŸ™‚ The issue is that the real Lambda calls on YEARFRAC, and YEARFRAC does not play nice when fed vectors.  So I must iterate over the dataset.

Resources