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

 

[smiler]

my knowledge of excel is probably proportionate to the number of posts i have compared to others, i am by no means an expert, i just dabble now and again, i have only been using excel for 12 months but do have a VB background, all self taught no formal training. when i first saw this challenge i couldnt even get started, then it hit me with the using numbers and converting them to a lookup

once i had this working, i sat and smiled for a couple of hours.

im a ****** for a challenge!

 

[hady]

Voila!! - fully working, fully automated

have removed the count functions (tried to future proof in the original so you could have more than nine words, but this would never have worked as 10 would have been seen as 1 and 0, so have reset all counts to be a static 9

=IF(NOT(ISTEXT(B3)),IF(LEN(B3)=10,INDEX(A1:A100,MID(RIGHT(B3,10),  1,1))&INDEX(A1:A100,MID(RIGHT(B3,9), 1,1))&INDEX(A1:A100,MID(RIGHT(B3,8),1,1))&INDEX(A1:A100,MID(RIGHT(B3,  7),1,1))&INDEX(A1:A100,MID(RIGHT(B3,6),1,1))&INDEX(A1:A100,MID(RIGHT(B3, 5),1,  1))&INDEX(A1:A100,MID(RIGHT(B3,4),1,1))&INDEX(A1:A100,MID(RIGHT(B3,3),1,1))&INDEX(A1:A100, MID(RIGHT(B3,2),1,1)),IF(ISERROR(FIND(RIGHT(B3,  1),LEFT(B3,LEN(B3)-1))),VALUE(B3&(1+(INT(RAND()*9)))),VALUE(LEFT(B3, LEN(B3)-2)&1+INT(RAND()*9)))),1+INT(RAND()*9))

sorry if this is not paginated correctly, not sure how putting spaces in would help

S/\/\iler

That's absolutly random Smiler, very good result
your formula even better cause able to automated refresh.
you must be working hard to get it with Mega formula

Hady

 

[hady]

this should be real random (or as random as Rand() will give)

one litle bug, when you enter this, if the rand() produces a value that it already has it will stop and you need to press f9 to recalculate, generally only have to do this once to get the answer

Smiler,
Your formula is perfect, there is no bug. specially on your last version.
if you see numbers on result means your iteration hit the maximum 1000 times already.
if you set iteration 10000, there will be no bug.
this because you need more than 1000 iteration to produce unique numbers in 9 digit sometimes, once you put 10000 iteration your formula works perfectly...

Hady :wink:

 

[fairwinds]

Just noticed this thread

How about a normal formula that does not require tampering with iterations?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT(A1&" ",8),A1&" ",A2&" ",RANDBETWEEN(1,8)),A1&" ",A3&" ",RANDBETWEEN(1,7)),A1&" ",A4&" ",RANDBETWEEN(1,6)),A1&" ",A5&" ",RANDBETWEEN(1,5)),A1&" ",A6&" "&INDEX($A$1:$A$9,ROUNDUP(RIGHT(NOW())/3,0)+6)&" ",RANDBETWEEN(1,4)),A1&" ",CHOOSE(ROUNDUP(RIGHT(NOW())/3,0),A8,A7,A7)&" ",RANDBETWEEN(1,3)),A1&" ",CHOOSE(ROUNDUP(RIGHT(NOW())/3,0),A9,A9,A8)&" ",RANDBETWEEN(1,2))

Pretty random I would say.

 

[hady]

Just noticed this thread

How about a normal formula that does not require tampering with iterations?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPT(A1&" ",8),A1&" ",A2&" ",RANDBETWEEN(1,8)),A1&" ",A3&" ",RANDBETWEEN(1,7)),A1&" ",A4&" ",RANDBETWEEN(1,6)),A1&" ",A5&" ",RANDBETWEEN(1,5)),A1&" ",A6&" "&INDEX($A$1:$A$9,ROUNDUP(RIGHT(NOW())/3,0)+6)&" ",RANDBETWEEN(1,4)),A1&" ",CHOOSE(ROUNDUP(RIGHT(NOW())/3,0),A8,A7,A7)&" ",RANDBETWEEN(1,3)),A1&" ",CHOOSE(ROUNDUP(RIGHT(NOW())/3,0),A9,A9,A8)&" ",RANDBETWEEN(1,2))

Pretty random I would say.

nice try fairwinds.
This is what i called set of random, it is not real random.
after "word6" is always followed by "word7" or "word8" or "word9"
your random sentence can not produce "word6" followed by "word1" to "word5".
So this is not real random sentence, but your methode can be used in define names, means you can use substitute more than 7 times and combine with rand() function will do the job.

hady

 

[fairwinds]

You're hard to please hady :wink:

What about this idea? Is it random?
(I know it fails sometimes when it does not recalc fast enough.)

{=INDEX(A1:A9,INDEX(RIGHT(NOW()/ROW(1:49)),LARGE(IF(FREQUENCY({1,2,3,4,5,6,7,8,9},--RIGHT(NOW()/ROW(1:49))),ROW(1:49)),1)))&INDEX(A1:A9,INDEX(RIGHT(NOW()/ROW(1:49)),LARGE(IF(FREQUENCY({1,2,3,4,5,6,7,8,9},--RIGHT(NOW()/ROW(1:49))),ROW(1:49)),2)))&INDEX(A1:A9,INDEX(RIGHT(NOW()/ROW(1:49)),LARGE(IF(FREQUENCY({1,2,3,4,5,6,7,8,9},--RIGHT(NOW()/ROW(1:49))),ROW(1:49)),3)))&INDEX(A1:A9,INDEX(RIGHT(NOW()/ROW(1:49)),LARGE(IF(FREQUENCY({1,2,3,4,5,6,7,8,9},--RIGHT(NOW()/ROW(1:49))),ROW(1:49)),4)))&INDEX(A1:A9,INDEX(RIGHT(NOW()/ROW(1:49)),LARGE(IF(FREQUENCY({1,2,3,4,5,6,7,8,9},--RIGHT(NOW()/ROW(1:49))),ROW(1:49)),5)))&INDEX(A1:A9,INDEX(RIGHT(NOW()/ROW(1:49)),LARGE(IF(FREQUENCY({1,2,3,4,5,6,7,8,9},--RIGHT(NOW()/ROW(1:49))),ROW(1:49)),6)))&INDEX(A1:A9,INDEX(RIGHT(NOW()/ROW(1:49)),LARGE(IF(FREQUENCY({1,2,3,4,5,6,7,8,9},--RIGHT(NOW()/ROW(1:49))),ROW(1:49)),7)))&INDEX(A1:A9,INDEX(RIGHT(NOW()/ROW(1:49)),LARGE(IF(FREQUENCY({1,2,3,4,5,6,7,8,9},--RIGHT(NOW()/ROW(1:49))),ROW(1:49)),8)))&INDEX(A1:A9,RIGHT(NOW()))}