Advanced Formula Challenge #12: Results and Discussion 5

Last week I set readers the challenge which can be found here.

Such was the number and variety of responses to this challenge that presenting a detailed breakdown of one such solution – as has been the case for all of the first eleven in this series of challenges – would, I feel, be somewhat inappropriate.

For the majority of these challenges, it could be argued that there has been one solution which is indisputably “better” than the rest. Perhaps such an adjudication can also be made here, though to do so would certainly not be a straightforward exercise. What’s more, to pick just one of the many solutions would be to leave the rest – unfairly in my opinion – left on the sidelines.

As such, I would refer the readers to the many solutions in that post and to enjoy dissecting the varied and wonderful constructions therein. And to simply thank all those – Alex, aMareis, Maxim, John Jairo, sam, Jeff, Lori, Ron, Michael, Christian and XLarium – whose excellent contributions led to such a fruitful and inspiring discussion.

There’s evidently still much to be discovered in the world of worksheet formulas!

Another challenge to follow shortly. Watch this space!

5 comments

  1. I’m in the process of writing up a brief breakdown for each of the major approaches, as I thought it would be a good supporting example for the book I’m writing to illustrate the old adage “There’s many ways to skin a cat”. (Furthermore, it underscores a key point I make in the book that participating in on-line challenges like this – or even just trying to understand the solutions – is a great way to learn new approaches, or see formulas/formula combinations in action that you otherwise would never likely try out. Basically, it’s free university.)

    I’ll post a link to a sample file here fairly soon that contains these write-ups, and will put them on the book blog in more detail too, once the book is launched. (Probably in a month or two).

  2. An interesting site but I am not sure how to navigate it to find contributed solutions to the challenges. I was interested to compare my solution to challenge 12 with others.

    =MATCH(1,data.row,0)

    where data.row:

    =INDEX(data,k,)

    and k:

    =ROW(data)-ROW(INDEX(data,1,))+1
  3. Hi Peter,

    Many thanks for the contribution!

    Each of the “Results and Discussion” parts to the Challenges begins with a sentence along the lines of “Last week I set readers the challenge which can be found here”, where the “here” is a hyperlink to the actual challenge, at the bottom of which you’ll see the various attempted solutions by other readers.

    Regards

  4. Thanks for your reply; I have found the contributed solutions.

    I guess 2015 is ancient history now though. I am also not sure that my usual Excel programming style would fit in too well. I haven’t used a direct cell reference for many years now and have noticed that I rarely find it necessary to use filled ranges and relative referencing; it’s almost always a sequence of array formulas using names to avoid overt nesting of the formulas.

    This on the grounds of ‘keep it simple’ but others do not see it that way!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s