# Questions from the 2013 Modeloff awards ceremony

Show 40 post(s) from this thread on one page
Page 2 of 3 First 123 Last
• Sep 5th, 2014, 03:54 AM
Peter_SSs
Re: Questions from the 2013 Modeloff awards ceremony
Quote:

Originally Posted by DanMayoh
But that wasn't what they were asked to do, so don't be too hard on them :)

No, fair enough. :)

Quote:

Originally Posted by DanMayoh
... devise the shortest formula that they could, which would solve the precise challenge in front of them.

OK, given that (and lots more time to ponder) I now offer a modification of the stated solution that solves the "precise challenge" with just 30 characters. Still array-entered:

SUM(\$E\$18:\$H\$21*(B15:E18=100))
• Sep 7th, 2014, 08:44 PM
DanMayoh
Re: Questions from the 2013 Modeloff awards ceremony
Hi Peter, good pick up, and you raise an interesting point. You're right this solves the "precise challenge", but it doesn't reflect the intention of my previous comment, which I suppose I poorly explained. I meant that they needed a formula which would work for the precise workbook in front of them, but still be flexible enough to handle changes to the 'user-variable' cells in the workbook. In other words, the formula should be able to accommodate changes to the numbers, which were formatted as user-variable, but does not need to accommodate things like people adding or deleting rows/columns. Discussions like this are very useful for the next time I may run something similar, as they will help me articulate the guidelines better, so thank you!

I know I didn't include a formatting legend, but for the competition on the day this was ok because the competitors had seen the ModelOff organisers use the same formatting all day and all knew those yellow cells were to be taken as user-variable. If you take your approach to the extreme, then for Q3 posted at the top of this thread you could just write "=158", which I think we'd all agree should not be awarded as the shortest solution. Cheers.
• Sep 8th, 2014, 08:12 AM
Peter_SSs
Re: Questions from the 2013 Modeloff awards ceremony
Quote:

Originally Posted by DanMayoh
I meant that they needed a formula which would work for the precise workbook in front of them, but still be flexible enough to handle changes to the 'user-variable' cells in the workbook. In other words, the formula should be able to accommodate changes to the numbers, which were formatted as user-variable, ...

Dan
I'm not trying to get into an argument here and I like the challenges set, but I am getting a little confused.

My original assumption was as you state here - that the 'Original Square' of numbers could be any numbers. However, the accepted solution does not meet this test - as I pointed out earlier in this thread.
Following that you stated the following from which I could only conclude that the Original Square was not changeable.
Quote:

.. were given the explicit instruction to devise the shortest formula that they could, which would solve the precise challenge in front of them.
To recap my point from post #5 relating to this:
Quote:

Originally Posted by Peter_SSs
Whilst the stated solution does produce a correct result for the given layout and given data, it is not robust regarding other data sets. .... 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.

In the example below I've highlighted one changed Original Square cell and the resulting incorrect answer cells.

<b>challenge2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&#160;</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-weight:bold; ">Original Square</td><td >&#160;</td><td >&#160;</td><td >&#160;</td><td >&#160;</td><td style="font-weight:bold; ">Your Answer</td><td >&#160;</td><td >&#160;</td><td >&#160;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">100 </td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">111 </td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">112 </td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">114 </td><td >&#160;</td><td style="color:#0000ff; text-align:right; ">197 </td><td style="color:#0000ff; text-align:right; ">193 </td><td style="color:#0000ff; text-align:right; ">186 </td><td style="color:#0000ff; text-align:right; ">174 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="background-color:#99cc00; color:#0000ff; text-align:right; ">100 </td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">126 </td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">135 </td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">136 </td><td >&#160;</td><td style="background-color:#ffcc00; color:#0000ff; text-align:right; ">348 </td><td style="background-color:#ffcc00; color:#0000ff; text-align:right; ">342 </td><td style="background-color:#ffcc00; color:#0000ff; text-align:right; ">333 </td><td style="background-color:#ffcc00; color:#0000ff; text-align:right; ">316 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">142 </td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">147 </td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">149 </td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">151 </td><td >&#160;</td><td style="background-color:#ffcc00; color:#0000ff; text-align:right; ">287 </td><td style="background-color:#ffcc00; color:#0000ff; text-align:right; ">284 </td><td style="background-color:#ffcc00; color:#0000ff; text-align:right; ">273 </td><td style="background-color:#ffcc00; color:#0000ff; text-align:right; ">242 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">174 </td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">186 </td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">193 </td><td style="background-color:#fff58c; color:#0000ff; text-align:right; ">197 </td><td >&#160;</td><td style="background-color:#ffcc00; color:#0000ff; text-align:right; ">250 </td><td style="background-color:#ffcc00; color:#0000ff; text-align:right; ">247 </td><td style="background-color:#ffcc00; color:#0000ff; text-align:right; ">237 </td><td style="background-color:#ffcc00; color:#0000ff; text-align:right; ">200 </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >J18</td><td >{=SUM&#40;\$E\$18&#58;\$H\$21*<span style=' color:008000; '>&#40;B15&#58;E18=\$E\$18&#41;</span>&#41;}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array&#58;</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web &#62;&#62; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

The upshot for me then is that the "winners" of this particular challenge were a little lucky to be awarded that status. :)
• Sep 8th, 2014, 11:49 AM
DanMayoh
Re: Questions from the 2013 Modeloff awards ceremony
Of course you are right Peter, and I'm not trying to argue either. All of your comments have been very useful!
The same thought occurred to me after I posted last my comment - namely that if the top left value was repeated somewhere, the 'winning' formula wouldn't work. And you had indeed already pointed this out in earlier comments, which I forgot about when writing my comment.
I didn't pick this constraint up on the competition day as I literally had about 10 seconds to adjudicate formulas, and I didn't in that time fully think through the ramifications of how the formula worked. (This particular aspect of the competition was a "fun" event in front of a live audience, and did not have any bearing on the main ModelOff result.)
If a competitor on the day used your "...=100))" formula, perhaps I would have judged it worthy of winning - especially if they pointed out the shortcoming in the "...=\$E\$18))" formula.
Apologies for being imprecise in my earlier comments. As I said before, these are valuable lessons for me in the event ModelOff runs something similar again!
• Sep 8th, 2014, 07:05 PM
Peter_SSs
Re: Questions from the 2013 Modeloff awards ceremony
OK, understand the difficulties in making an on-the-spot decision on the day. :)
• Sep 18th, 2014, 05:00 AM
Nicolas Favre
Re: Questions from the 2013 Modeloff awards ceremony
Here is my working solution to #4 with only 34 characters :

=MAX(,INT((H\$17-\$E19)/\$F19/365+1))
• Sep 22nd, 2014, 08:10 AM
stefankemp
Re: Questions from the 2013 Modeloff awards ceremony
Quote:

Originally Posted by Peter_SSs
No, fair enough. :)

OK, given that (and lots more time to ponder) I now offer a modification of the stated solution that solves the "precise challenge" with just 30 characters. Still array-entered:

SUM(\$E\$18:\$H\$21*(B15:E18=100))

Nice solution Peter. Don't think you can get shorter than that.
• Oct 14th, 2014, 12:10 PM
gigidica
Re: Questions from the 2013 Modeloff awards ceremony
This is an excellent solution. My solution was:
=INDIRECT("R"&ROW(J18)+2*(19-ROW(\$J18))+1&"C"&COLUMN(J18)-2*(COLUMN(J\$18)-9),FALSE)
It gave me a length of 83 and I agree that is not as robust....
:)
• Oct 22nd, 2014, 05:47 AM
notmcgee

https://s3-us-west-2.amazonaws.com/s...l-Golf+(1).pdf

https://s3-us-west-2.amazonaws.com/s...-Golf2+(1).pdf

Click No for 1st Cert Prompt, then Click Yes Subsequently.
• Oct 22nd, 2014, 06:18 AM
notmcgee
Quote:

Originally Posted by notmcgee