I was recently helping someone to develop a formula-based Conditional Formatting rule, a stipulation for which (at least in 2010) is that the explicit use of array constants is forbidden.
Of course, we can get around this by first storing these array constants as Defined Names and then referencing those in the formula instead, which is perfectly legitimate.
On that particular day, however, I was in a rather creative mood (no doubt to the OP’s detriment!), and so went about the (somewhat convoluted) task of creating a version of my formula in which any array constants were replaced with suitable functional constructions, only to find that this process was not always quite as straightforward as I had first presumed…
Inspired by that event, the challenge this week is as follows: quite simply, to generate the shortest possible construction which will result in an output of:
Solutions may not of course make use of any array constants – that would be rather self-defeating! Solvers should not, however, confuse array constants with constants, use of the latter being perfectly permissible.
No assumptions may be made about the content of any cell within the worksheet. Note also that vector-type is non-negotiable: constructions resulting in an output of:
As usual, any ranges must include both a row and column reference: A:B or 1:2, for example, are not acceptable. Named Ranges are also not permitted.
Note that this is a shortest formula challenge, which means that readers should attempt to find not only a correct solution to the problem but also one which has the least number of characters as possible.
Solution next week. Best of luck!