Forum Discussion
MAP x MAP
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.
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λ)
- 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.