Questions from the 2013 Modeloff awards ceremony

[KeyCuts]

There were 4 finals challenge questions the contestants had to answer in teams during the awards ceremony for Modeloff 2013.

With the help of Dan Mayoh from the Modeloff question team, we were able to get a copy of these questions and provided our own explanation to solve these challenges. Here are the original questions with explanations posted on our blog:

1. Question 1 of 4: Count Number of Days between Start and End Dates
2. Question 2 of 4: Rotate Values in 4X4 Range
3. Question 3 of 4: Count Number of Values Between -10 and +10 (Follow up explanation on Challenge #3)
4. Question 4 of 4: Count Number of Flags That Have "Set Off"

Leave us a comment if you were able to solve the challenges using a different formula than the stated answer!

 

[Peter_SSs]

Thanks for providing these questions.

I decided to have a look at Question 2 and have a few comments, though I understand you are only providing what has been provided to you.

I was surprised ...

1. ... to see that the definition of most "efficient" formula was "the formula with the least number of characters" as opposed to, say, "the formula that calculated the fastest".

2. ... that the stated solution was given as such, with no "robustness" required. I have two reasons:

(i) I did not see in the given written information that the "original square" must start in cell D4 or below/right, it just refers to "a range of numbers next to the blue cells". Yet if the original square does start above/left of D4, the stated solution is not a solution at all.

(ii) The stated solution also relies on certain other things happening (or not happening) in the cells above and left of the "original cells" For example, if cell B15, which has nothing to do with the stated challenge, has the value 100 entered into it, the stated solution returns, without warning, an incorrect result in cell J18.

3. ... that the particular OFFSET solution was given as an alternative. Again it relies on certain unrelated events not happening. For example, if any rows/columns above/left of the "original square" are either deleted or inserted, the solution produces incorrect results.


For what it is worth, I would have used this much longer formula (but more robust and easily scalable to a larger grid - unlike the INDEX alternative suggested) in J18, copied across and down.
=INDEX($E$18:$H$21,ROWS($E$18:$H$21)-ROWS(J$18:J18)+1,COLUMNS($E$18:$H$21)-COLUMNS($J18:J18)+1)

 

[jorgeluis500]

I tend to agree with Peter regarding the OFFSET formula. It's short but somewhat unstable. Kudos for creativity though, including the other formulas provided in the comments section.

For the other two mentioned in the post, hats off

My formula was =INDEX($E$18:$H$21,ROWS(E18:$H$21),COLUMNS(C5:$F$8))

 

[KeyCuts]

Thanks for the comment Peter! When I was working through the solution (I took the solution and worked backwards to figure out how it works), I was surprised to see that the answer utilized cells outside of the original square. As you said, those cells have really nothing to do with the main square.

Having said that, given the time crunch the contestants were given and the constraints of the challenge, this solution is still very creative in my opinion.

OFFSET() is definitely inefficient when it comes to large amounts of data, and I have seen it slow down many of my files. But as you said Jorge, it's short and sweet when you need to get the job done!

 

[Peter_SSs]

My formula was =INDEX($E$18:$H$21,ROWS(E18:$H$21),COLUMNS(C5:$F$8))

This is a good concept, though again it lacks some robustness (easily fixed) as it relies on a range outside (at least partly) the data and results ranges (the red part). With this solution in place, try inserting a new column D.

So, adjusting that red part to refer to the data range instead and, in trying to adopt the efficient = shorter concept, perhaps this:

=INDEX($E$18:$H$21,ROWS(18:$21),COLUMNS(E:$H))



Returning to the original problem/stated solution, I have a further problem. Whilst the stated solution does produce a correct result for the given layout and given data, it is not robust regarding other data sets. Apart from the layout & external data issues I raised in my previous post, this solution relies on the top left cell being unique within the data set. With 100 still in cell E18, change any of the other cells in the "original square" to 100 and observe the results.

BTW: Welcome both of you to the MrExcel board!

 

[CROY1985]

For what it's worth, this is how I answered Q4:

=MAX(ROUNDDOWN(((COLUMN()-MATCH($E19,$17:$17,0))/($F19*4))+1,0),0)

Working from the middle outwards:

• Used the Column()-match to build a running count from 1 for the 1st flag up to x.
• Divided this by the number of periods before flag increase.
• Had to use rounddown and the addition of the +1 to position the 1st flag correctly.
• Used max to get rid of the negative results in the first few columns.

 

[DanMayoh]

Hi Peter and others,
You are offering some great comments! I'd just like to quickly clarify that on the day of the competition, the contestants were given the explicit instruction to devise the shortest formula that they could, which would solve the precise challenge in front of them. You are absolutely correct that in many cases this will not be the best formula to use in a real-world situation, and may not be robust enough to handle a similar problem under different conditions. But that wasn't what they were asked to do, so don't be too hard on them
And I admit that when I composed this question, I was not anticipating answers that would use cells outside of the square, but it was within the rules that I set, so I certainly give it points for clever thinking.

 

[DanMayoh]

You can download the file used by the contestants here
http://www.modeloff.com/wp-content/...f-2013-Finals-Group-Challenge-Excel-Golf.xlsm
(or follow the link at Excel Golf | ModelOff 2014 )

If you are concerned about opening up a workbook with macros, I can assure you that the only macros in it are some UDFs used to assist in checking the formula length. They are completely harmless!

 

[jorgeluis500]

This is a good concept, though again it lacks some robustness (easily fixed) as it relies on a range outside (at least partly) the data and results ranges (the red part). With this solution in place, try inserting a new column D.

So, adjusting that red part to refer to the data range instead and, in trying to adopt the efficient = shorter concept, perhaps this:

=INDEX($E$18:$H$21,ROWS(18:$21),COLUMNS(E:$H))



Returning to the original problem/stated solution, I have a further problem. Whilst the stated solution does produce a correct result for the given layout and given data, it is not robust regarding other data sets. Apart from the layout & external data issues I raised in my previous post, this solution relies on the top left cell being unique within the data set. With 100 still in cell E18, change any of the other cells in the "original square" to 100 and observe the results.

BTW: Welcome both of you to the MrExcel board!

You're right Peter. My mistake. I originally solved it the way I mentioned it but with the correct references and since I copy and paste the formulas and keep the files to build my own "Excel knowledge database", somehow I did not update this one when I rearranged the cells. BTW, when I tried it for the first time I ended up with a solution similar to yours.

https://twitter.com/jorgeluis500/status/410157815001407488

Thanks for the welcome!

 

[jorgeluis500]

But that wasn't what they were asked to do, so don't be too hard on them

Ha, quite the opposite Dan. Being a fan of Modeloff I truly admire every one of the competitors and I keep learning from you all

Keep the good work