Shortest Formula Challenge!! (Games 1-3)

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

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

good point Martin, I will move it there

well i got the word one down to 70 characters

32 & 70 .

down to 32 now i over used ()

snap shg 32 &70

Oops, I lied -- 32 & 75.

i assume its for that text only as given?

down to 32 & 62

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.

well in that case
wait for it
45 for the text

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

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

30 & 54. Working on the word one though

Word Starter: 41

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

Originally Posted by 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.

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

Originally Posted by clabulis

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

yep other i were considered in the making of this formula
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!

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

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.

Game 2 is attached below. Good luck!

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

Originally Posted by martindwilson

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.

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

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

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

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

Originally Posted by 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

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?

61 for after last -

Originally Posted by martindwilson

61 for after last -

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

Regards

well 51 if n/a is allowed

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

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

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!

Originally Posted by romperstomper

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

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

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

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

Got the Student Averages down to 53.

Edit: down to 51 now.

Regards

Originally Posted by sailepaty

I can't see the formula

You didn't follow the instructions?

Originally Posted by clabulis

Highlight the area below each title to view them.

Regards

arrgh crafty subtotal.....

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

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?

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

@martindwilson

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

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

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

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

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

Can any one explain me what is the actual question?

Sixth - do you know what a prime number is?

Regards

Originally Posted by XOR LX

Sixth - do you know what a prime number is?

Really don't know what it is.... lol

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

Originally Posted by XOR LX

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

Now I understand the question perfectly

Originally Posted by :) Sixthsense :)

Wow thanks for the nice explanation with examples

Now I understand the question perfectly

Sure!

Got 30 for Prime Number and 163 for Letter Frequency so far...

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

Originally Posted by clabulis

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

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

Originally Posted by 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!

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!

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

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

Originally Posted by 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.

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

Cheers

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

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

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

why ? its not an official competition is it?,

Originally Posted by clabulis

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!

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

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

=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.

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)