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:

{-1;1}

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:

{-1,1}

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!

### Like this:

Like Loading...

*Related*

Newcomer to this site. My starting point would be something like

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

Can’t get much shorter than those. Good Job pjc.

Who can beat pjc? )))

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

Or maybe:

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

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

columnvector, or, if you prefer, in terms of worksheet orientation,vertical.Ops. It would be Maxim’s solution. Mine isn’t allowed.

I guess that unfortunately XOR LX’s comment was addressed to me.

Hans, but idea was your! ))

Just for theoretical fun, to avoid relative volatility of ROW():

Another for-fun one that avoids ROW():

oops misread the instructions, this instead:

Another for-fun:

Another concoction:

If used for conditional formatting, then: