Questions from the 2013 Modeloff awards ceremony

KeyCuts

New Member
Joined
Aug 19, 2014
Messages
2
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
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)
 
Last edited:

jorgeluis500

New Member
Joined
Aug 26, 2014
Messages
3
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

New Member
Joined
Aug 19, 2014
Messages
2
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
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!
 
Last edited:

CROY1985

Active Member
Joined
Sep 21, 2009
Messages
498
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

New Member
Joined
Jun 29, 2014
Messages
4
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.
 

jorgeluis500

New Member
Joined
Aug 26, 2014
Messages
3
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

New Member
Joined
Aug 26, 2014
Messages
3
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
 

Forum statistics

Threads
1,078,447
Messages
5,340,349
Members
399,371
Latest member
wilbot

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top