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!
 
The Modeloff Finals and Awards Ceremony will be held at Microsoft's New York offices (1290 Avenue of the Americas, New York) at 2:00pm on sunday !
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Re: golf answer

Hi all,

Just discovering this board while preparing for round 2 of Model 2014. In case anyone else came across it, I thought I'd share some thoughts on the solutions 2013 excel golf challenge and to the challenge itself, as I was there.

I believe many of the solutions presented can be improved upon.
(1) @notmcgee: Your solution is almost exactly the same as the one I came up with, but you can improve it slightly, by removing the 0, leaving a length of 38 characters.
=MAX(MIN($F18,H$17)-MAX($E18,H$16)+1,)
=max(min($f18,h$17)-max(h$16,$e18)+1,0)
(2) As some present at the competition, I would like to say that it was very clear what the challenge was and the intent of the rules, and that Peter's solution, while shorter, was not a valid one as it depended on the values entered into the inputs. While I came up with a slightly better solution that the contestants while at the competition, it wasn't until afterwards that I came up with what I believe is the best solution: =OFFSET($I$30,9-ROW(),9-COLUMN())
Length: 33 characters
This solution does have one trick, which is that it uses a box that is outside the input grid in order to slightly shorten the formula.
(3) This too is one where I had a better solution than the contestants while at the competition, but not nearly as good as one I came up with after the competition. And that even that was beat by a colleague of mine, who improved it by 1 character. His solution is:
{=SUM(N(E20:S59^2>100))}
Length: 22 characters
(4) I have the same solution that Nicholas Favre has, namely:
=MAX(INT((H$17-$E19)/$F19/360+1),)
Length: 34 characters

As someone present at the competition, I would like to say that I loved the idea of this challenge and I thought it was very exciting, lots of fun, and clearly challenged the minds of the competitors. I especially liked that as a observer, I was able to play along with the competitors in my head. In response to other people's comments, I would like to emphasize that it was very clear what the challenge was and the intent of the rules. It was also clearly tricky to review results quickly. That said, it didn't feel like the solutions were well thought out in advance, and so even there, some of us were able to come up with better solutions than the ones presented.

Thanks again for putting together such a great competition!!!
 
Upvote 0
My answers: -
Question 1
=IF(AND($E18<=H$17,$F18>=H$16),MIN(H$17-$E18,H$17-H$16,$F18-H$16,$F18-$E18)+1,0)
Question 2
=INDEX($E$18:$H$21,ROWS(18:$21),COLUMNS(E:$H))
Question 3
=COUNTIF(E20:S59,">10")+COUNTIF(E20:S59,"<-10")
Question 4
=MAX(0,INT((H$17-$E19)/$F19/365+1))
 
Upvote 0
Re: golf answer

Hi all,
I believe many of the solutions presented can be improved upon.
(1) @notmcgee: Your solution is almost exactly the same as the one I came up with, but you can improve it slightly, by removing the 0, leaving a length of 38 characters.
=MAX(MIN($F18,H$17)-MAX($E18,H$16)+1,)

Just discovering this board myself, and was checking here to see if my answer was optimal. Turns out I've improved on the best answer here for the golf challenge.

Change the reference style to R1C1.
"=MAX(,MIN(R17C,RC6)-MAX(R16C,RC5)+1)" 36 characters, but the checking formula is using the A1 formula to evaluate, and doesn't pick up the difference.
 
Upvote 0
Re: golf answer

Shorter doesn't mean more transparent and effective.
My solution of 2nd question is:

=INDEX($E$18:$H$21;COUNT(E18:E$21);COUNT(E18:$H18))

Which doesn't use nor hard coded values, no cells outside the initial array - all of them are the potential source for future errors.
Also its easy to use the same formula with the arrays of different dimensions.
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top