Forum Discussion

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

PeterBartholomew1

Silver Contributor

Apr 01, 2023

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λ)

XMAP.xlsx17 KB

ecovonrein

Iron Contributor

Apr 02, 2023

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.