FORMULA CHALLENGE shortest wins !

[erik.van.geit]

Hi, everyone !!
just a little idea

In this threadwe came up with some formulas to get a random character choosen from
a, b, c ... z, A, B, C, ... Z, 0, 1, 2 ... 9
so 62 possibilities

wouldn't it be nice to make a little sport of it

the one who generates the shortest formula, may launch the next formulachallenge

OBJECTIF
GENERATE randomly 1 LCASE or UCASE or INTEGER
rules:
1. formula in english
2. acurate = all characters must occur about as much as the others when formula is tested (for instance 20000 times, copied down or "evaluate"-macrotest)
3. posts must be generated within 7 days (168 hours) from now
4. no replies with comments about the other formulas
5. jury = moderators ?

what do you think ?

kind regards,
Erik

 

[tightwad]

47 charaters by chrisfang, a member of http://club.excelhome.net

=CHAR(-SMALL(-{31,64,96}-ROW(1:26),RAND()*62+1))

This only works for A or a on my computer

 

[erik.van.geit]

47 charaters by chrisfang, a member of http://club.excelhome.net

=CHAR(-SMALL(-{31,64,96}-ROW(1:26),RAND()*62+1))

This only works for A or a on my computer

I didn't test when I replied, just saw the resemblance with "wintercoms" formula
you shoul enter this formula with Control+Shift+Enter
so it's an arrayformula

 

[hady]

New Challenge, shortest formula win

Since no one submit new challenge, let me issue one :
I have data on column A1 to A9,
word1
word2
word3
word4
word5
word6
word7
word8
word9
can you make a formula in a cell B3, that can retrive randomly concatenate all 9 words in column A without repeat any single word.
example :
B3 = "word5word2word1word9word6word8word7word3word4"
by pressing F9 (calculate) sentence in cell B3 suppose to be randomly changes without any single word is repeated
Note :
1. challenge duration is 1 month
2. shortest formula is winner
3. not allowed to use UDF/macro
4. Erik you are the jury...

 

[Stormseed]

To:malcom & C. O. Jones

Thanks for your care. I am Chinese and living in Xiamen. My Enlish is so poor to write more words to express my opinion. I hope to be accepted here. Thanks again

hey mate...Excel is universal...Race does not matter as far as you are a human being and have a brain...we fight here to see who utilizes the brain in a smarter way....english is the medium of communication on this board but I reckon everyone seems to understand the broken one ! take care

 

[erik.van.geit]

Hi, hady,

I think it's better you started a new thread for your challenge
this one will be lost in the crowd
wait and see if you want

and ... there have been some challenges after this one

kind regards,
Erik

 

[Andrew Fergus]

For the latest challenge, I have assumed every entry in cells A1 to A9 is the same length, and used this formula :

{=IF(LEN(B3)<SUM(LEN(A1:A9))+1, IF(ISERROR(FIND(RIGHT(B3, LEN(A1)), LEFT(B3, LEN(B3)-LEN(A1)))), B3&INDEX(A1:A9, INT(RAND()*10), 0), LEFT(B3, LEN(B3)-LEN(A1))), RIGHT(B3,SUM(LEN(A1:A9))))}

confirmed with Ctrl+Shift+Enter and with the Tools > Options > Calculation > Iteration > set to 1000 and keep pressing F9 for the formula to complete itself.

It's a lot of characters (I'm embarrassed to say there are 175 characters excluding the '{','=' & '}'), so it probably won't be the shortest but let's get the ball rolling and see what else pops up......

Andrew

 

[erik.van.geit]

Hady,

I posted a link to here in the Lounge.

To my sense your challenge didn't assume all words would have the same lenght.

Anyway Andrew,
your formula is an engineering-masterpiece in my eyes For others trying out the formula: it must be put in cell B3

best regards,
Erik

 

[Andrew Fergus]

Hi Erik

This is actually a really difficult challenge. I had to make the assumption on the word length because if you use the RAND() function inside a formula twice it will return 2 different values, so there is no way of testing the value about to be appended without first appending it. If you tried to test it with one RAND() and then added it using another RAND() function, then the value appended is unlikely to be the value you tested.

If I have to append it before I test it, and I don't know which one was appended nor how long it was, then I don't know how many characters to test to see if it is a repeat. I suppose I could change it to only look at the last n characters where n is the minimum of the length of the range A1:A9 and assume that the last n characters are unique (e.g. Word1 to Word9 is ok, but Word & 1Word is not). But again that isn't 100% right.

The smarter approach would be to eliminate the values already used from the 'population' but I have no idea how to do that in a formula.

BTW, hady should get credit for the formula design - hady proposed something identical in hatman's palindrom question, see here:
http://www.mrexcel.com/board2/viewtopic.php?p=1130767&highlight=#1130767

This would be much easier using VBA....

Andrew

 

[erik.van.geit]

Yes,

I'm quite aware of the difficulty using a single formula
a formulasystem could even be "shorter", I mean counting all characters used in different cells

hady, palindrome, Oh, yes of course , I knew I had seen that name before somewhere: could have checked it ...

best regards,
Erik

EDIT: no, I don't think that a formulasystem would be "shorter"
B1 to B9 =RAND()
formula:

=INDEX(A1:A9,RANK(B1,B1:B9))
&INDEX(A1:A9,RANK(B2,B1:B9))
&INDEX(A1:A9,RANK(B3,B1:B9))
&INDEX(A1:A9,RANK(B4,B1:B9))
&INDEX(A1:A9,RANK(B5,B1:B9))
&INDEX(A1:A9,RANK(B6,B1:B9))
&INDEX(A1:A9,RANK(B7,B1:B9))
&INDEX(A1:A9,RANK(B8,B1:B9))
&INDEX(A1:A9,RANK(B9,B1:B9))

no concatenate-arraytrick for this ?

 

[smiler]

Doh! - i worked on this before re-reading the rules (no UDF),

thought i would post it anyway,

=joinwords(rand()) ' Rand makes f9 recalculate work. - theres probably even a better way to write a UDF, but i was happy i managed to create one that worked

Function joinwords(tt)
Dim words(9) As String
d = 0
Do Until done1
x = Int(Rnd() * 10) + 1
If x < 1 Or x > 9 Then
Else
wrd = Workbooks(ThisWorkbook.Name).ActiveSheet.Cells(x, 1)
For i = 0 To 9
If words(i) = wrd Then
i = 9
dun = 1
Else
End If
Next i
If dun <> 1 Then
words(d) = wrd
d = d + 1
End If
If d = 9 Then done1 = 1
End If
dun = 0
Loop
For t = 0 To 9
word = word & words(t)
Next t
joinwords = word
End Function