Forum Discussion
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() with 1. (Gives 1 1.)
PPS: Putting the SUM() inside a named second Lambda (which would return 5) doesn't improve things.
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.
- Never mind. SCAN did not work me just now either, even straight from the manual. Seems that my Excel session had lost the plot. Shut it all down and started again, 5 5. All good.
- I spoke too soon. This function is utter hit&miss. In a new session, it will work. And then at some point it packs up. Together with SCAN. That too packs up at random. Unbelievable.
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.
Forgive me for asking, but what are you trying to achieve? The formulas do not actually use the contents of the row arrays you reference except to count the cells in one and repeat the result.
No shame in asking. The real thing - I only post the reductio ad absurdum to highlight the issue - periodifies multiple invoices (MAP#1) into multiple accounting periods (MAP#2). The whole experience has left me traumatized. At one point I was receiving results in every other cell. I am sure that there is a bug. But what that might be, we will never find because it is sadly (but reliably) intermittent.
My guess
=SEQUENCE( ,COLUMNS(C23:D24), COLUMNS(C5:G7), 0 )returns exactly the same result
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.
