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:

`{-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!

1. pjc says:

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

`2*ROW(A1:A2)-3`
2. ikkeman says:
`="{-1;1}"`

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

3. Will says:

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

4. Maxim Zelensky says:

Who can beat pjc? )))

5. Hans Knudsen says:

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:

`=-1^COLUMN(A:B)`

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

6. Hans Knudsen says:

Or maybe:

`=-1^COLUMN(A1:B1)`
7. Maxim Zelensky says:

so may be then

`-1^ROW(A1:B1)`

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

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

8. 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.

9. `=-1^ROW(1:2)`
10. Ops. It would be Maxim’s solution. Mine isn’t allowed.

11. Hans Knudsen says:

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

12. Maxim Zelensky says:

Hans, but idea was your! ))

13. Maxim Zelensky says:

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

`=FREQUENCY(2,1)*2-1`
14. Alex Groberman says:

Another for-fun one that avoids ROW():

`=INDEX(MUNIT(2),,1)*2-1`
15. Alex Groberman says:

`=INDEX(MUNIT(2),,2)*2-1`
16. Alex Groberman says:

Another for-fun:

`=MODE.MULT(-1,-1,1,1)`
17. Alex Groberman says:

Another concoction:

`=TRANSPOSE(LINEST(1)*2-1)`
18. If used for conditional formatting, then:

`=-1^ROW(\$1:\$2)`

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