Questions from the 2013 Modeloff awards ceremony
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Questions from the 2013 Modeloff awards ceremony

  1. #1
    New Member KeyCuts's Avatar
    Join Date
    Aug 2014
    Location
    NYC
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Questions from the 2013 Modeloff awards ceremony

     
    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!

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    36,422
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Questions from the 2013 Modeloff awards ceremony

    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 by Peter_SSs; Aug 26th, 2014 at 08:00 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    New Member
    Join Date
    Aug 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Questions from the 2013 Modeloff awards ceremony

    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))

  4. #4
    New Member KeyCuts's Avatar
    Join Date
    Aug 2014
    Location
    NYC
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Questions from the 2013 Modeloff awards ceremony

    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!

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    36,422
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Questions from the 2013 Modeloff awards ceremony

    Quote Originally Posted by jorgeluis500 View Post
    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 by Peter_SSs; Aug 27th, 2014 at 06:26 PM. Reason: Typo
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    Board Regular
    Join Date
    Sep 2009
    Posts
    471
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Questions from the 2013 Modeloff awards ceremony

    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.

  7. #7
    ModelOff Moderator DanMayoh's Avatar
    Join Date
    Jun 2014
    Location
    Australia
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Questions from the 2013 Modeloff awards ceremony

    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.

  8. #8
    ModelOff Moderator DanMayoh's Avatar
    Join Date
    Jun 2014
    Location
    Australia
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Questions from the 2013 Modeloff awards ceremony

    You can download the file used by the contestants here
    http://www.modeloff.com/wp-content/u...xcel-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!

  9. #9
    New Member
    Join Date
    Aug 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Questions from the 2013 Modeloff awards ceremony

    Quote Originally Posted by Peter_SSs View Post
    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/sta...57815001407488

    Thanks for the welcome!

  10. #10
    New Member
    Join Date
    Aug 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Questions from the 2013 Modeloff awards ceremony

      
    Quote Originally Posted by DanMayoh View Post
    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com