Shortest Formula Challenge #5: Results and Discussion 2

A couple of weeks ago I set readers the challenge which can be found here.

Surprisingly, no-one managed to come up with the shortest solution. Dozens of attempts came very close, yet all were either ineligible or syntactically incorrect.

As such, the shortest correct solution was also the very first received, courtesy of pjc, viz:


So many congratulations to pjc, even the more so as this was their very first post at this site! Fantastic start, pjc!

I have to confess to being very surprised here. Although it is true that some confusion arose as a result of different systems using different symbols to represent row and column vectors, this is not an issue which is insurmountable: as part of my contributions to various Excel forums, I have often been in a position in which it was necessary to amend a solution such that any array constants were of a syntactically-valid form for the system being used by the OP, even if this syntax differed from that employed by my version of Excel. A few minutes researching the topic was sufficient to inform me of the required amendments.

What’s more, I made explicit which type of vector was required for the solution as early as comment #8, though this appears to have been in vain.

There were also several solutions which, although syntactically correct, failed to adhere to the stipulations of the challenge as laid out in the preamble, in particular that which forbade the use of range references which do not include both a row and column reference.

As such, the shortest solution, and that which was missed by all, is:


upon sight of which, I imagine, all of Hans Knudsen:


(returns the wrong vector-type)

Maxim Zelensky:


(A1 and B1 are both in row 1!)

Felipe Costa Gualberto:


(violates stipulation re range references)

and David Hager:


(violates stipulation re range references)

will be kicking themselves.

These issues aside, the challenge did raise some interesting points. In fact, I have to confess that I made a bit of a hash of this one, in the sense that I had originally intended it to be used as a premise for provoking investigations into means of generating array constants via functions which were specifically suitable for general Conditional Formatting rules.

What’s more, I had also intended to exclude the ROW function from this exercise and so open up a debate as to alternative means of generating such arrays.

Unfortunately, neither of these two features were made explicit, and so the challenge ended up being a little besides the original point, to say the least.

Still, some nice, alternative constructions from Alex and Maxim for those who thought it couldn’t be done without the use of the ROW function. And I suppose that ikkeman should be commended for such ingenuity!

Thanks again to all who contributed. Another challenge to follow shortly. Watch this space!


  1. Hey that’s cool, thanks 🙂

    Usually I avoid these types of things because I have some (misguided?) belief that there’s *no way* I’ll get the correct answer.

    Looking forward to the next challenge!

  2. Interestingly, if the result was taken to be a row vector (as some assumed based on local settings) I believe the answer would be similar to one of Alex’s suggestions:


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.