Shortest Formula Challenge!! (Game 8: Plates & Rand. Char.)

yeah i though of the same for random character but i suspect clabulis wants us to isolate unique values so
ouiwerjbvjkhvyusdnkvbsdjbjkbljkbsvvbgasdflwefpakjgheg
becomes
abdefghijklnoprsuvwy
or its just been over thought

Random Character 103 so far.

Regards

Originally Posted by clabulis

My solution for License Plates is really bothering me. I feel like there's a much better way to solve it than the brute force method that I used. Hopefully someone can figure out a trick for this one!

I really don't believe that a more concise approach is available for the License Plates challenge.

The two criteria are sufficiently different to make a single approach unfeasible. What's more, since the array in question is already two-dimensional, any attempts at querying - en masse - individual characters within all strings within that array will run into the obvious problem of running out of "dimensions": for example, attempting to abbreviate e.g.:

=SUM(MID(G2:P26,4,1)+MID(G2:P26,5,1)+MID(G2:P26,6,1))

using a compound formula along the lines of:

=SUM(0+MID(G2:P26,{4,5,6},1))

would, in effect, require the "construction" of a three-dimensional grid to hold the relevant returns.

Of course, we can first manipulate the range G2:P26 into a single-column or single-row array, so that the above construction is then viable, but since these challenges are all about minimizing formula length the cost here would simply be too high.

Regards

First attempt for license plates puts me at 170. Hopefully I can find some fluff. Some of the shortcuts I like to use are rendered useless with the 2 dimensional array...

excelliot - What happened to your answer of 22 (typo I assumed) and length of 115 previously posted?

I gotcha, I thought just the 22 was a typo but you still had 115.

Random Character at 119, first attempt that gave me an accurate result, let's see if I can shorten now..

Assuming any random string is going to be Lower Case letters only without spaces as the sample string is:

Edit
117!
------------------------
{=CHAR(LARGE(--(NOT(ISERR(FIND(CHAR(ROW(97:122)),G2))))*ROW(97:122),RANDBETWEEN(1,COUNT(FIND(CHAR(ROW(97:122)),G2)))))}
------------------------

I'll post mine for Random Character since Speshul has done so, which uses similar logic though comes in at 103 characters:

Array-entered:

=CHAR(AGGREGATE(14,6,FIND(CHAR(ROW(A:A)),G2)^0*ROW(A:A),RANDBETWEEN(1,COUNT(FIND(CHAR(ROW(A:A)),G2)))))

Regards

oi not fair,not everyone has 2011

Ooooh XOR LX. That's where I could have shortened mine down a couple characters...

I didn't need the "97:122 for the count on the randbetween since it's just a count"

I'm just happy I was able to solve it by myself

Originally Posted by martindwilson

oi not fair,not everyone has 2011

Says the man who proposed a solution using 2013's IFNA() the other week!

Originally Posted by Speshul

I'm just happy I was able to solve it by myself

Are you kidding? Yours was an excellent solution: I'm just a bit more practised now with these tricks to save characters!

Cheers

no i didnt, i just pointed out it was there..so formulas could be theoretically be made shorter, but i think this should be restricted to any of pre 2007,2007, or 2007 and greater or solutions calculated for each

License Plate: 12 plates found matching the conditions, 173 chars (below in white)

=SUMPRODUCT(--(RIGHT(G2:P26,1)+MID(G2:P26,4,1)+MID(G2:P26,5,1)<15)*--((LEFT(G2:P26,1)=MID(G2:P26,2,1))+(LEFT(G2:P26,1)=MID(G2:P26,3,1))+(MID(G2:P26,3,1)=MID(G2:P26,2,1))>0))

Originally Posted by martindwilson

no i didnt, i just pointed out it was there..so formulas could be theoretically be made shorter, but i think this should be restricted to any of pre 2007,2007, or 2007 and greater or solutions calculated for each

Why? 2010 is four years old now: at what point are we "allowed" to say that this version is "chronologically acceptable"?

I could equally argue that this should be for 2003 and earlier only - your choice of versions and how "old" they are is completely arbitrary, unless of course you have some statistics to support your claim, e.g.:

% of forum users who have 2003 only: 10%
% of forum users who have 2007 only: 80%
% of forum users who have 2010 only: 10%

Regards

i am never going to buy 2010/2013 not worth the expense
and i wont be here for the next version anyway

Stealing Speshul's formula, this is a slightly shorter version (110)

[COLOR="#FFFFFF"]{=CHAR(LARGE(ISNUMBER(FIND(CHAR(ROW(97:122)),G2))*ROW(97:122),INT(RAND()*COUNT(FIND(CHAR(ROW(97:122)),G2))+1)))[COLOR]

I can't test it but I guess Xor's formula could use the same construct for the second arguement of the large function to save a character as well.

Can someone post the shorter License plates? I got the answer ya'll are posting (12) but did it in 173 chars I would love to see some shorter formulas!

If someone already posted, please point me to it!

Originally Posted by ragulduy

Stealing Speshul's formula, this is a slightly shorter version (110)

[COLOR="#FFFFFF"]{=CHAR(LARGE(ISNUMBER(FIND(CHAR(ROW(97:122)),G2))*ROW(97:122),INT(RAND()*COUNT(FIND(CHAR(ROW(97:122)),G2))+1)))COLOR]

I can't test it but I guess Xor's formula could use the same construct for the second arguement of the large function to save a character as well.

Nice idea, ragulduy.

Regards

Originally Posted by GeneralDisarray

Can someone post the shorter License plates?

Try this one with 125 (array formula below in white)

=SUM((MID(G2:P26,4,1)+MID(G2:P26,5,1)+RIGHT(G2:P26)<15)*(1-(FIND(MID(G2:P26,2,1),G2:P26)=2)*(FIND(MID(G2:P26,3,1),G2:P26)=3)))

Thank you, I was finally able to find some after scrolling all responses a third time.

Pretty good ideas to add to my bag of tricks, thank you OP for another great game

I went right for sumproduct() - that's my go-to formula for multi-conditional problems like this.

Small variation to make it 117.....

=SUM((MID(G2:P26,4,1)+MID(G2:P26,5,1)+RIGHT(G2:P26)<15)*(FIND(MID(G2:P26,2,1),G2:P26)*FIND(MID(G2:P26,3,1),G2:P26)<6))

Am I missing something on the Random Characters, it seems much easier than all the other games posted.

I get 33 chars -- are we supposed to do more than just return a single character?

=MID(G2,RANDBETWEEN(0,LEN(G2)),1)

Originally Posted by GeneralDisarray

Am I missing something on the Random Characters, it seems much easier than all the other games posted.

I get 33 chars -- are we supposed to do more than just return a single character?

=MID(G2,RANDBETWEEN(0,LEN(G2)),1)

Have you ensured that every character present in the string has an equal probability of being returned? Remember that not all letters of the alphabet may necessarily be in that string.

Regards

Originally Posted by ragulduy

Stealing Speshul's formula, this is a slightly shorter version (110)

[COLOR="#FFFFFF"]{=CHAR(LARGE(ISNUMBER(FIND(CHAR(ROW(97:122)),G2))*ROW(97:122),INT(RAND()*COUNT(FIND(CHAR(ROW(97:122)),G2))+1)))[COLOR]

I can't test it but I guess Xor's formula could use the same construct for the second arguement of the large function to save a character as well.

Stole it back, and shortened by three more characters! (107)

=CHAR(LARGE(ISNUMBER(FIND(CHAR(ROW(97:122)),G2))*ROW(97:122),INT(RAND()*COUNT(FIND(CHAR(ROW(A:A)),G2))+1)))

Originally Posted by XOR LX

Are you kidding? Yours was an excellent solution: I'm just a bit more practised now with these tricks to save characters!
Cheers

:D

Honestly, before about four weeks ago when I finally saw a visual to whats going on inside an array, I didn't have a clue how they worked, or when they were useful.

These games have about doubled my understanding of formulas.

I have a thought for the final games;

Participants should save each formula attempt or version, failed or not, and when solutions are posted here post your 'log' of versions to show exactly how your formula evolved and changed, it would also show the numerous methods which can be utilized to achieve the same result. I know personally I was playing around with >CHOOSE< before i decided to go with a >LARGE/RANDBETWEEN< combination.


I think that would be pretty useful for those 'watching from the sidelines' on these (which is me, on most of them!)

Originally Posted by XOR LX

Have you ensured that every character present in the string has an equal probability of being returned? Remember that not all letters of the alphabet may necessarily be in that string.

Regards

Ohhhhhhhhhhhhhhhhhhhhhhhh I see now. That's a bit different :D Thank you

Originally Posted by Speshul

These games have about doubled my understanding of formulas.

Couldn't agree more, which is why I made a point before going on holiday to thank clabulis for beginning these challenges. I myself believe I have improved by a considerable degree having started to tackle these problems.

Regards

That solution you guys found for random Char is completely new to me I'm going to have to make sure i didn't miss any of these games - I've only caught two of the workbooks i think. I'm decent with arrray formulas but these games have definitely been novel problems for the general.

Thank you clabulis!

I was wondering the same thing. I suppose now I understand (hopefully), if the string were to be aaaaaaaaaab, the formula should have a 50% chance of returning a or b. Is that correct?

Originally Posted by Hawkeye16

I was wondering the same thing. I suppose now I understand (hopefully), if the string were to be aaaaaaaaaab, the formula should have a 50% chance of returning a or b. Is that correct?

I believe so, yes.

Regards

@speshul or someone, can you explain why you aren't using something like

=CHAR(LARGE(ISNUMBER(FIND(CHAR(ROW(A:A)),G2))*ROW(A:A),INT(RAND()*COUNT(FIND(CHAR(ROW(A:A)),G2))+1))

(changed row references)

instead? It seems to return the same and is shorter but I am sure there is a reason. It seems odd to use it in the second part but not the first part to me.

Originally Posted by Hawkeye16

@speshul or someone, can you explain why you aren't using something like

=CHAR(LARGE(ISNUMBER(FIND(CHAR(ROW(A:A)),G2))*ROW(A:A),INT(RAND()*COUNT(FIND(CHAR(ROW(A:A)),G2))+1))

(changed row references)

instead? It seems to return the same and is shorter but I am sure there is a reason. It seems odd to use it in the second part but not the first part to me.

Perhaps he just forgot. Have you looked at all the previous posts in this thread? You'll see that that construction has been used if you do...

Regards

Oh. Interesting. So I had the shortest formula for this one afterall

Well, learning experience! Here is why I overlooked this:

I think the reason is how I build my formulas. I build step by step, then manually go back and combine them all when I'm done.

So, in my early versions of the Random Character spreadsheet I started with using: (White font)

Q5:Q30 {=ROW(97:122)}
R5:R30 {=--(NOT(ISERR(FIND(CHAR(ROW(97:122)),G2))))}
S5:S30 =Q5*R5

This gave me, in S5:S30 a visual of the array list, so I could see {97,98,0,0,101,0,0,0,...etc} , for when "aaabbebb" is in G2. I do this so I can see how the formula is functioning in steps, to troubleshoot specific sections instead of one long formula

If I use A:A, these formulas do not accurately return the CHAR number of the letter found. As I continued to develop and tweak the formula for shortness, I disregarded the idea that A:A could be used because it couldn't before. However, you are correct that the final product does return the right values with A:A.

My last post of 107 chars (#36 on this thread), where I used both 97:122 AND A:A was because I still believed I needed to find/return using a 97:122, but the RANDBETWEEN(1,n) value I wanted to search for could havebeen any number, so the {1,2,3,4,5,...} that A:A would return would be perfectly acceptable for this section of the formula.

I hope this helps explain the confusing mess that is my thought process!

Originally Posted by Speshul

Oh. Interesting. So I had the shortest formula for this one afterall

What length are you declaring now, then? Does that beat by 103 from post #19? And remember that I still haven't incorporated ragulduy's recommendation of INT(RAND(... which should save another character...

Or do you agree with Martin that post-2007 solutions should be disallowed?

Also, why have you suddenly decided to reveal parts of your solution for all to see? I thought we were staying "white font" for the time being?

Regards

Ah you're right, I forgot that the modified 107 length version of mine originally modified by ragulduy, was modified to 101 posted by Hawkeye


So...my original formula post of 117 would be shortened down to 108, not 101.

Also, forgot the whitefont...applied now!

Nice to see the process. I should do something more like that in the future. Currently I write the whole thing as I believe it should be and troubleshoot by highlighting certain sections and pressing F9 to see what it evaluates to in order to troubleshoot.