Forum Discussion
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()...
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.
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.
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.
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.
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.
