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