MAP x MAP

[ecovonrein]

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.

[#2]

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.

[#3]

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.

[#4]

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.

[#5]

@ecovonrein 

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.

[#6]

@ecovonrein 

My guess

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

returns exactly the same result

[#7]

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.

[#8]

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.

[#9 · Accepted solution]

@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.

[#10]

"YEARFRAC tends to play better if you put a + sign in front of multi-cell range references to convert them to arrays" Great tip, Peter! That eliminates the need for the inner MAP in this case. (Though I see my particular algorithm divorced from the wider issue that MAP(MAP(... does not work reliably as it should.)

" the correct answer to your problem" While I agree that the inability to return VSTACKs from MAP is extremely irritating, I can recognize that limitation as consistent within the design framework chosen. (I have less sympathy that BYCOL suffers from the same restriction.) I do not believe, however, that it would answer to the original problem. The inner MAP in my original proposition only returns a vector, which is SUMmed such that the outer MAP also only constructs a vector. As I wrote, you might even find the formula to work at first try. And then perhaps fail later in the day.  

[#11]

Do you know a similar trick to force MAX(a,b), MIN, AND, ... to return a vector of pair-wise results as opposed to the single result of the union?

[#12]

@ecovonrein 

Sadly, no simple tricks.  The nearest for MAX(a, b) is

= IF(a>b, a, b)

 which will broadcast to give a 2D array of results.

Otherwise one could write a Lambda function XMAP to generalise MAP to broadcast the two parameters to give a 2D array, which will then be processed by the inbuilt MAP function.

XMAP(x, y, Fnλ)
= MAP(Broadcastλ(x, y), Broadcastλ(y, x), Fnλ)

Broadcastλ(x, y)
= IF(SIGN(y), x)

Maxλ(x, y)
= MAX(x, y)

Minλ(x, y)
= MIN(x, y)

The worksheet formulae would then be

= XMAP(date, period, Maxλ)

= XMAP(date, period, Minλ)

[#13]

Yeah. I used IF to replace MAX/MIN and * to replace AND. (The latter is the same problem with FILTER.) In truth, the code that this produces (to obviate the inner MAP) is harder to follow than MAP(MAP(... And my suspicion is that there aren't even any efficiencies in the vector version since YEARFRAC must now loop internally over the date brackets thrown at it.

Hey ho. It is what it is. And your suggestion works. So I settle for that. Thanks.