Shortest Formula Challenge #5: No Array Constants, Please 18

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:


are invalid.

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!


  1. ="{-1;1}"

    Yes, I realize this is probably cheating, but it’s the end of friday before a long weekend, so 😉

  2. I never thought I would dare to participate here and I also suspect that I may have misunderstood what is allowed/not allowed. Anyhow I will try with this one:


    (On my system it returns {-1\1})

  3. so may be then


    will give us {-1;1}. 13 symbols.

    Can’t check, on my system there are : for rows separator and ; for columns

  4. Obviously there is some confusion resulting from various systems using different symbols to represent row and column vectors.

    To clarify, the returned vector should be a column vector, or, if you prefer, in terms of worksheet orientation, vertical.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.