# Off Topic > The Water Cooler >  >  Shortest Formula Challenge!! (Games 1-3)

## clabulis

For those of you who like to test your formula skills, I developed a series of games (10 in all) where you can try to come up with the shortest formula possible to complete the objective.  There are 2 challenges per game.  Full rules are explained within the file.  I'll post my best solutions a little later.  Good luck!

(Game #1 is attached)



*Note:  The challenges are targeted for advanced Excel users.  They range in difficulty... but even the "easier" ones can be tricky.*

See post #32 for Game 2 file
See post #64 for Game 3 file

----------


## martindwilson

this really needs moving to the watercooler 
but i can't get shorter than 34 at the moment for no 7 sum!

----------


## FDibbins

good point Martin, I will move it there

----------


## clabulis

Sorry about the original post being put in the wrong forum!  Hopefully some more people will still give this a try   :Roll Eyes (Sarcastic):

----------


## martindwilson

well i got the word one down to 70 characters

----------


## shg

32 & 70         .

----------


## martindwilson

down to 32 now i over used ()

----------


## martindwilson

snap shg 32 &70

----------


## shg

Oops, I lied -- 32 & 75.

----------


## clabulis

Nice!  Looking at your 70 lengths for the Word Starter problem, it made me want to go back and try it again.  Got it down to 69.  :Smilie:

----------


## martindwilson

i assume its for that text only  as given?

----------


## clabulis

Your formula needs to accommodate any possible text that could sit in cell G2.  The maximum amount of text that a cell can hold is 32,767 characters.

----------


## martindwilson

down to 32 & 62

----------


## martindwilson

yes but i mean all sentences will be properly constructed with spaces , commas/ full stops /line breaks or whatever so it reads correctly
eg this bit
And the song, from beginning to end,
I found again in the heart of a friend.
if it didnt have a line break it would be

And the song, from beginning to end, I found again in the heart of a friend.
not
And the song, from beginning to end,I found again in the heart of a friend.

----------


## clabulis

Oh yeah I see what you mean now. Line breaks must stay exactly as they are. If an "i" begins any word, including a word that follows immediately after a line break, it must be accounted for.

----------


## martindwilson

well in that case
wait for it
45 for the text

----------


## clabulis

Wow! Haha I'll take one more look at it later tonight, but I'm not getting my hopes up. I'll post my solutions tomorrow to give some more people a chance of trying it themselves.

----------


## snb

You could save us some time telling what the amount of 'i' 's should be in your opinion.

----------


## Norie

Do you really mean 'i' or is it 'i' or 'I'?

----------


## clabulis

> You could save us some time telling what the amount of 'i' 's should be in your opinion.



The solution of 17 is provided on the first tab in the attachment.





> Do you really mean 'i' or is it 'i' or 'I'?



Both uppercase and lowercase I's should be included.  A quick count of the I's in the paragraph to match the given solution will confirm this.

----------


## clabulis

> well in that case
> wait for it
> 45 for the text



Got it down to 47.  Not finding those extra two characters though...

----------


## Ace_XL

30 & 54. Working on the word one though  :Wink:

----------


## snb

Word Starter: 41

----------


## martindwilson

down to 43 now for word too many () in last formula again

----------


## XOR LX

> Your formula needs to accommodate any possible text that could sit in cell G2.  The maximum amount of text that a cell can hold is 32,767 characters.



Ah. I see. So we need to take into account the possibility of there being words of length more than one and ending in "i", which are not in the current text, e.g. "Circles with radii of various lengths"?

I only mention as this might be a potential barrier to solutions hoping to look for the personal pronoun "I" via counting for "I "...

Regards

----------


## clabulis

> Ah. I see. So we need to take into account the possibility of there being words of length more than one and ending in "i", which are not in the current text, e.g. "Circles with radii of various lengths"?
> 
> I only mention as this might be a potential barrier to solutions hoping to look for the personal pronoun "I" via counting for "I "...
> 
> Regards




Hmm, I don't want to confuse people, but I'll to try to explain it as simply as possible:  If the first character of any word is an "i" or an "I", then count that letter.  The personal pronoun of "I" is a word, so those instances of I's need to be counted.  I's which lie somewhere in the middle of a word or at the end a word, are not counted.

Your formula needs to work for any block of text that could possibly sit in G2 as well.  If the text was "I eat ice cream in an igloo", then your formula needs to return a result of 4.

----------


## XOR LX

> Hmm, I don't want to confuse people, but I'll to try to explain it as simply as possible:  If the first character of any word is an "i" or an "I", then count that letter.  The personal pronoun of "I" is a word, so those instances of I's need to be counted.  I's which lie somewhere in the middle of a word or at the end a word, are not counted.
> 
> Your formula needs to work for any block of text that could possibly sit in G2 as well.  If the text was "I eat ice cream in an igloo", then your formula needs to return a result of 4.



Thanks. 

I only mentioned it as I imagine others like me may have developed a solution along the lines that I mentioned, which would work perfectly well for the given text, though, as I said, could well fail if we actually consider a different text in G2, one which contains words ending in "i", as I mentioned.

I was just wondering if the solutions so far have factored this in as a consideration?

Regards

----------


## martindwilson

yep  other i were considered in the making of this formula  :Smilie: 
mind you i cant be arsed to put a 32,767 character passage into a cell count all the Is then check with a formula!

----------


## XOR LX

And I assume that adapting for upper-case instances of the personal pronoun will not also be sufficient, since (and perhaps relevant to your poetry example) we could equally be considering a "modern" text containing lower-case examples of "I", e.g. Bukowksi, etc.?

Regards

----------


## clabulis

> mind you i cant be arsed to put a 32,767 character passage into a cell count all the Is then check with a formula!



Yeah I dont blame you haha.  I think once solutions begin being posted, people will come to a general consensus of if they're accurate or not.






> And I assume that adapting for upper-case instances of the personal pronoun will not also be sufficient, since (and perhaps relevant to your poetry example) we could equally be considering a "modern" text containing lower-case examples of "I", e.g. Bukowksi, etc.?



Your formula should give you 17 as the result even if you manually change the case of any "I" within the text.



Just another reminder to everyone:  no manipulation of the raw data! (as stated in the instructions in the file).  For example, don't type a space before that first "I" in order to make it easier to deal with in your formula.  Your formula will not be counted.  Though, of course you can change the raw data temporarily to test your formula with various text strings.

----------


## clabulis

I'm going to post my solutions below in white font to prevent spoilers to those still attempting them.  I ask everyone else to do the same when posting your own solutions.  Thanks!

Highlight the area below each title to view them.

*No Seven Sum (28 Characters)*
=SUM(SUBSTITUTE(0&G:H,7,)*1)

(Must use Ctrl+shift+enter)

*Word Starter (47 Characters)*
=SUM(N(MID(" "&G2,ROW(1:32767),2)={" I","
I"}))

(Must use CSE)
NOTE:  The split in the formula is a line break, thus accounting for that scenario within the sample text.

----------


## clabulis

Game 2 is attached below.  Good luck!

----------


## martindwilson

for sum =SUM(--SUBSTITUTE(G2:H291,7,"")) array 
hmm for word i went with
=LEN(G2)-LEN(SUBSTITUTE(PROPER(G2),"I",""))
as proper will always capitalise after any space line break or punctuation that i could determine

----------


## clabulis

> hmm for word i went with
> =LEN(G2)-LEN(SUBSTITUTE(PROPER(G2),"I",""))
> as proper will always capitalise after any space line break or punctuation that i could determine




Awesome!

Your method can actually be shortened a little to this:
=LEN(G2)-LEN(SUBSTITUTE(PROPER(G2),"I",))
Making it 41 characters.  I wonder if it's the same solution as snb back when he posted on page 2 with 41 characters.

----------


## snb

Mine:

=SUM(N(TRIM(MID(G2;ROW(1:32768);2))="i"))

I second Martin's solution for sum: it's more in conformity with  the specifications than yours. G:H <>G2:H291

----------


## martindwilson

hm snb i cant get that to work for say just "I'm Ian"

----------


## clabulis

> Mine:
> 
> =SUM(N(TRIM(MID(G2;ROW(1:32768);2))="i"))
> 
> I second Martin's solution for sum: it's more in conformity with  the specifications than yours. G:H <>G2:H291




Interesting method!  I think the concern that XOR LX brought up before regarding words ending in "i" will affect this result though.  Through in the word "alumni" and the count goes up to 18.


Remember that efficiency is not a factor when determining these formulas.  Most of these won't be used in real-life scenarios because of this.  In fact, the column-only reference (G:H) trick is gonna play a big part in a lot of these challenges.  The trick still results in the formula fitting the definition of what is required since we know other data won't be added to other cells such as G548 or H1, etc.

----------


## martindwilson

Interesting challenges but for example sumproduct is 10 characters
But if ms had named it sump it wold be shorter

----------


## clabulis

> Interesting challenges but for example sumproduct is 10 characters
> But if ms had named it sump it wold be shorter



Thanks!  Yeah, a lot of what goes into picking the shortest formula is knowing which functions to use.  I never use SUMPRODUCT due to its length.  SUM usually suffices as an array formula.

----------


## XOR LX

I have 56 and 62 for Game2 so far, so evidently I'm contesting your "Optimisation" of 10 for Last Instance!

_Edit: Ah, wait. Just saw your caveat re "No hyphens = No text", so scratch that 56..._

Regards

----------


## XOR LX

> I have 56 and 62 for Game2 so far, so evidently I'm contesting your "Optimisation" of 10 for Last Instance!
> 
> _Edit: Ah, wait. Just saw your caveat re "No hyphens = No text", so scratch that 56..._
> 
> Regards



Actually, re this can you just confirm that the answer should be blank in the case where there are no hyphens in the string, and not e.g. #N/A?

Regards

----------


## clabulis

> Actually, re this can you just confirm that the answer should be blank in the case where there are no hyphens in the string, and not e.g. #N/A?
> 
> Regards



You're correct that a blank should be returned if there are no hyphens.

----------


## martindwilson

wel the student one has got me stumped dont even lnow how to aproach it!(well i thought i did nothing worked)
the text one however does this still apply up to =2^15-1 characters?

----------


## martindwilson

61 for after last -

----------


## XOR LX

> 61 for after last -



Returning blank if no hyphens, or #N/A? Still not sure which is expected(?)

Regards

----------


## martindwilson

well 51 if n/a is allowed

----------


## clabulis

#N/A is not allowed for the last instance problem.* If there are no hyphens in the text string, then the returned solution should be completely blank.* Martin, it sounds like you might have the exact same solution that I came up with for that one since 51 and 61 are my lengths as well.

----------


## martindwilson

ha you must have redone it then since you have 65 on the sheet lol

----------


## clabulis

Haha yeah I changed the files last night actually. You must've gotten the first one.

----------


## romperstomper

61 for student averages - assuming we don't have to count the {}?

----------


## martindwilson

i can see the arrays i want but have no idea how to sum or average them! eg {86,87,88;1,2,3;60,70,80}
i want either 
{261;6;210} or {87;2;70} its driving me mad!

----------


## XOR LX

> 61 for student averages - assuming we don't have to count the {}?



Well - I imagine you have to count them if they're part of an actual array constant! 

I still can't beat 62 with the "=".

Regards

----------


## romperstomper

No, I mean the array formula part. I figure I didn't type them so they don't count.  :Smilie:

----------


## martindwilson

{} dont count but = is so say the rules of course you can shortern some of these slightly with ifna in 2013 rather than iferror

----------


## XOR LX

Ah! There's an IFNA() in 2013?!

----------


## XOR LX

Got the _Student Averages_ down to *53*. 

_Edit: down to 51 now._

Regards

----------


## clabulis

I won't be fully able to update this thread or comment on it for another 6 hours or so because of work, but I'm excited to hear more of what you guys came up with.

----------


## sailepaty

> I'm going to post my solutions below in white font to prevent spoilers to those still attempting them.  I ask everyone else to do the same when posting your own solutions.  Thanks!
> 
> Highlight the area below each title to view them.
> 
> *No Seven Sum (28 Characters)*
> =SUM(SUBSTITUTE(0&G:H,7,)*1)
> 
> (Must use Ctrl+shift+enter)
> 
> ...



I can't see the formula

Thanks

----------


## XOR LX

> I can't see the formula



You didn't follow the instructions?





> *Highlight the area below each title to view them.*



Regards

----------


## clabulis

> Got the _Student Averages_ down to *53*. 
> 
> _Edit: down to 51 now._
> 
> Regards




I got mine down to 61 but I have no idea how to cut it back any further!!

----------


## clabulis

I'm going to post my solutions to Game 2 below in white font to prevent spoilers to those still attempting them.  I ask everyone else to do the same when posting your own solutions.  Thanks!

Highlight the area below each title to view them.

*Last Instance (61 Characters)*
=IFERROR(RIGHT(G2,MATCH("-",LEFT(RIGHT(G2,ROW(A:A))),)-1),"")

(Use CSV)

*Student Averages (61 Characters)*
=SUM(LARGE(SUBTOTAL(1,OFFSET(H2,ROW(1:100),,,26)),{1,2,3}))/3

(Use CSV)

----------


## martindwilson

arrgh crafty subtotal.....

----------


## newdoverman

No Seven Sum. I could only get down to 30 characters.

Array enter

=SUM(SUBSTITUTE(G2:H291,7,)*1)

Found the correct number of words but formula horribly long :Frown:

----------


## clabulis

Game 3 is attached below.  I had a huge struggle with "Letter Frequency" in this one.  Took me forever!  Good luck!

----------


## martindwilson

ok i'm with you on 30 char but isn't

=LEN(G2)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2,"i ","1 ")," i"," 1"),"I","1"),"1",""))
101 characters?

----------


## martindwilson

solutions for game 2 please!
i'll post mine as i has no idea about the averages and i liked your a:a idea so you win
=IFERROR(RIGHT(g2,MATCH("-*",RIGHT(g2,ROW(1:32767)),0)-1),"")
highlight above to see

----------


## newdoverman

@martindwilson

I just realized that the idea was to get the shortest formula to accomplish the task also applied to the WORD challenge :Frown:   My solution just won't cut it.

----------


## clabulis

I'm really curious to see XOR LX's word solution.  I'm completely stumped.

----------


## XOR LX

_Student Averages_ (again, in white font), but *non-array*(!!):

=SUM(LARGE(MMULT(H3:AG102,ROW(1:26)^0),{1,2,3}))/78

Looking forward to Game #3 now - loving this thread!

Cheers

----------


## :) Sixthsense :)

Instead of Keeping a single thread, I would like to see it in separate threads for each games.

Shortest Formula Challenge *1*

Shortest Formula Challenge *2*

Shortest Formula Challenge *3*

Shortest Formula Challenge *4*

Like that, so that the discussions of those games stick with those threads  :Smilie:

----------


## :) Sixthsense :)

I am unable to understand the Game 3 - Challenge #1 question  :Confused: 





> G2 contains a number.		
> Any number up to, and including, one million



Can any one explain me what is the actual question?

----------


## XOR LX

Sixth - do you know what a *prime number* is?

Regards

----------


## :) Sixthsense :)

> Sixth - do you know what a *prime number* is?



Really don't know what it is....  :Confused:  lol  :Wink:

----------


## XOR LX

It's a number for which the only numbers which divide into it without a remainder are 1 and the number itself.

For example, 12 is NOT a prime number, since 2, 3, 4 and 6 are all factors of 12 (12/2=6, 12/3=4, 12/4=3, 12/6=2).

But 17 IS a prime number, since nothing (*apart from 1 and 17 itself*) divides into 17 without a remainder.

Cheers

----------


## :) Sixthsense :)

> It's a number for which the only numbers which divide into it without a remainder are 1 and the number itself.



Wow thanks for the nice explanation with examples  :Smilie: 

Now I understand the question perfectly  :Wink:

----------


## XOR LX

> Wow thanks for the nice explanation with examples 
> 
> Now I understand the question perfectly



Sure!  :Smilie: 

Got 30 for _Prime Number_ and 163 for _Letter Frequency_ so far...

----------


## XOR LX

I think the _Letter Frequency_ needs a bit of clarification. 

For example, what if the text uses less than 9 of the 26 letters of the alphabet? What result should the formula give then? 

And if two or more letters occur equally frequently then any of those is fine to return?

Regards

----------


## clabulis

> _Student Averages_ (again, in white font), but *non-array*(!!):
> 
> =SUM(LARGE(MMULT(H3:AG102,ROW(1:26)^0),{1,2,3}))/78
> 
> Looking forward to Game #3 now - loving this thread!
> 
> Cheers




Nice!  I actually just read up on the MMULT function last night.  You beat me to it though!

Glad you're liking the thread too  :Smilie:

----------


## clabulis

> I think the _Letter Frequency_ needs a bit of clarification. 
> 
> For example, what if the text uses less than 9 of the 26 letters of the alphabet? What result should the formula give then? 
> 
> And if two or more letters occur equally frequently then any of those is fine to return?
> 
> Regards



You're right actually.  I'll have to think of that one a little more after work today.

----------


## clabulis

> Instead of Keeping a single thread, I would like to see it in separate threads for each games.
> 
> Shortest Formula Challenge *1*
> 
> Shortest Formula Challenge *2*
> 
> Shortest Formula Challenge *3*
> 
> Shortest Formula Challenge *4*
> ...



Good idea!  It'll be difficult to split the first three up now that they've already been discussed, but I can apply your suggestion to future releases.  Is there any way to edit the title of a thread?  So that this one can be changed to something like "Shortest Formula Challenge 1-3"?

----------


## :) Sixthsense :)

> Is there any way to edit the title of a thread?  So that this one can be changed to something like "Shortest Formula Challenge 1-3"?



Thanks for considering the suggestion  :Wink: 

Click on the *Edit button* *in the First Post* and *click Go Advanced* which will give you to option to edit the thread title, I believe  :Smilie:

----------


## clabulis

Got the letter frequency problem down to 155. Let's keep the formula requirements to apply strictly for the text that's already there, as opposed to being dynamic. 

Bonus points to the person who can make a fully dynamic function!

----------


## XOR LX

> Got the letter frequency problem down to 155. Let's keep the formula requirements to apply strictly for the text that's already there, as opposed to being dynamic. 
> 
> Bonus points to the person who can make a fully dynamic function!



_Prime Number_: *30*

_Letter Frequency_: 

*139* - Current text

*143* - Any text, presuming it contains such a ninth most frequently occurring character - you still haven't clarified what results should be given in the circumstances I outlined earlier.

Cheers!

----------


## XOR LX

In fact, I'm not sure it's such a great idea to make this one applicable to the current text only. 

All the preceding challenges have required a generalisable solution and, what's more, with this particular challenge one of the more difficult aspects is accounting for the fact that, in general, a combination of counts for both upper- and lower-case versions of each letter must be considered.

However, if it's just for this passage, there's nothing to stop me performing several manual calculations to verify that the result is unchanged by excluding the (fewer) upper-case letters in the text (i.e. that "h" still comes out top) and so removing any consideration for them from the solution.

If this is the case then I imagine I could take another 10-15 off my formula length.

Regards

----------


## clabulis

I agree with you completely. Formula must be dynamic. If two or more letters are tied for 9th, return any one of them. If there are less than 9 different letters, return a blank. I'll give it a try tonight when I get home from work.

----------


## GeneralDisarray

I got "no seven sum" to 34 , "word starter" I'm down to 86!  I'm just glad to get there with no helper column :D

Also, the only puzzles i see are the "No Seven Sum" and "Word Starter"... am i missing a updated puzzle book?

Edit:

Finally realized people were posting solutions with white font to hide spoiling... here's mine...I realize the numbers are high and really, really late to the party  :Smilie:  but i revisted this about 5 times today when i had a moment to spare until i got something... so i'm putting it up

No seven sum 34-  =SUM(1*SUBSTITUTE(G2:H291,"7",""))

Word Starter 86- =SUM(--(MID(" "&G2,ROW(1:426),2) = " I"),--(MID(" "&G2,ROW(1:426),2) = CHAR(10) &"I"))

----------


## XOR LX

> I agree with you completely. Formula must be dynamic. If two or more letters are tied for 9th, return any one of them. If there are less than 9 different letters, return a blank. I'll give it a try tonight when I get home from work.



Ok. Guess that additional clause is going to increase the formula length somewhat...

Cheers

----------


## XOR LX

Anyway, off on holiday now for two weeks, so will have to leave it there. 

I've added a clause to my _Letter Frequency_ formula in line with returning a blank if there are less than 9 distinct letters in the text, so now my best is *176*. 

Solutions in white below for anyone interested. 

Shame I'll be missing the rest (great fun, this!) but best of luck to all and hopefully there'll be more to come some time in the future (big thanks to *clabulis* for all the effort that's obviously been put in to these exercises!).

Cheers and see you soon!

(Both *array formulas***):

_Prime Number_: *30*

=SUM(0+(MOD(G2,ROW(A:A))=0))=2 

_Letter Frequency_: *176*

=IFERROR(CHAR(63+MATCH(IFERROR(1/(1/(LARGE(FREQUENCY(CODE(MID(UPPER(G2),ROW(A:A),1)&"@"),ROW(64:90)),10))),"|"),FREQUENCY(CODE(MID(UPPER(G2),ROW(A:A),1)&"@"),ROW(64:90)),)),"")

----------


## clabulis

> Anyway, off on holiday now for two weeks, so will have to leave it there. 
> 
> I've added a clause to my _Letter Frequency_ formula in line with returning a blank if there are less than 9 distinct letters in the text, so now my best is *176*. 
> 
> Solutions in white below for anyone interested. 
> 
> Shame I'll be missing the rest (great fun, this!) but best of luck to all and hopefully there'll be more to come some time in the future (big thanks to *clabulis* for all the effort that's obviously been put in to these exercises!).
> 
> Cheers and see you soon!
> ...



Awesome frequency solution!  I had something very similar, but your "@" usage was very clever.  Have a good time on holiday.

(I'm happy to hear people are liking this thread too!)

----------


## clabulis

*My Prime Number solution (29 Characters)*
=SUM(N(MOD(G2,ROW(A:A))=0))=2

My Letter Frequency solution was pretty lengthy after an inefficient IF statement, so I'm just gonna go with XOR LX's for now....

----------


## martindwilson

picky but row(a:a) is> 10^6  :Smilie:

----------


## :) Sixthsense :)

Hi clabulis,

One more suggestion from my end.  Please add the Game 4 link in Game 3 post, so that the followers of game 3 will aware of the game 4.

Please repeat this step on every games so that the chain will not get broken and the subscribed users of every games will be aware of the new games  :Smilie:

----------


## martindwilson

why ? its not an official competition is it?,

----------


## clabulis

> picky but row(a:a) is> 10^6



To be able to check for primes for the first 1048576 numbers is even better than just being able to check for the first 1000000 I think  :Wink:

----------


## clabulis

> Hi clabulis,
> 
> One more suggestion from my end.  Please add the Game 4 link in Game 3 post, so that the followers of game 3 will aware of the game 4.
> 
> Please repeat this step on every games so that the chain will not get broken and the subscribed users of every games will be aware of the new games



I think checking the main water cooler page would be the best way to find the new games honestly.  Remember, not everyone involved with these games has been here from the beginning.  And not everyone will wanna go in chronological order.

----------


## :) Sixthsense :)

> And not everyone will wanna go in chronological order.



Threads placement never stay in chronological order since based on the replies the threads go up and down.

So in future if anyone wanted to go through all the games then it is an hectic one in finding the sequence of games threads.

Just a thought!

----------


## FDibbins

> Remember, not everyone involved with these games has been here from the beginning. And not everyone will wanna go in chronological order.



you left out - and not everyone bothers lol

----------


## Hawkeye16

I have letter frequency at 125.  Please point out my error  :Smilie: 

=CHAR(MAX(IF(SMALL(LEN(SUBSTITUTE(UPPER(G2),CHAR(ROW(65:90)),)),9)=LEN(SUBSTITUTE(UPPER(G2),CHAR(ROW(65:90)),)),ROW(65:90))))

edit: doesnt work with less than 9 letters... I can't get it below 176 while accounting for that at first glance.

----------


## Haseeb A

Letter Frequency: 105, No error handler

=CHAR(64+RIGHT(LARGE((LEN(G2)-LEN(SUBSTITUTE(UPPER(G2),CHAR(ROW(65:90)),))&TEXT(ROW(1:26),"00"))+0,9),2))

No Seven Sum: 28 (use full column reference, G:H)

=SUM(SUBSTITUTE(0&G:H,7,)+0)

Last Instance: 50, No error handler

=MID(G2,LOOKUP(2^15,FIND("-",G2,ROW(A:A)))+1,2^15)

----------

