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

 

[PA HS Teacher]

Yours works perfectly if you want:
a number 1/3 of the time.
an upper case letter 1/3 of the time.
a lower case letter 1/3 of the time.

Let's say you generate 1000 random characters.
Yours would have a frequency something like:

y occurs 12.8 times
z occurs 12.8 times
...
Y occurs 12.8 times
Z occurs 12.8 times
...
1 occurs 33.3 times
2 occurs 33.3 times etc.
...

The original request is not that numbers appear as often as letters.
The original request is that every possible character appear equally often.
There are less numbers, so a number will come up 10/62 of the time, a lower case letter will come up 26/62 of the time, and an upper case letter will come up 26/62 of the time.

There are 26 upper case letters, 26 lower case letters, and 10 numbers for a total of 62 possible characters.

If 1000 characters are generated, the distribution should be

y occurs 16.1 times
z occurs 16.1 times
...
Y occurs 16.1 times
Z occurs 16.1 times
...
1 occurs 16.1 times
2 occurs 16.1 times
...
Y occucrs


Yours is a great solution, and it is certainly the shortest solution so far, but it does not ensure that each character appears with the same frequency.

 

[apolloh]

Oh, I see what the problem is.

Thanks for your patient!

 

[apolloh]

Not shorter but different

69 characters

=CHAR(SUM(({48;65;97}+{10;26;26}*RAND())*FREQUENCY(RAND()*31,{5,18})))

 

[apolloh]

62 characters

CHAR(IF(RAND()<5/31,RAND()*10,(RAND()>.5)*32+RAND()*26+17)+48)

 

[apolloh]

67 characters by yigepure

=CHAR(MAX({10;26;26}*RAND()+{48;65;97}*FREQUENCY(RAND()*31,{5,18})))

 

[apolloh]

This formula by asdf1001——a member of
http://club.excelhome.net

50 characters

=CHAR(LARGE({0,33,65}+ROW(32:57),INT(RAND()*62)+1))

Maybe sometimes we should plus "$" there.

52 characters

=CHAR(LARGE({0,33,65}+ROW($32:$57),INT(RAND()*62)+1))


Another formula not shorter but different by fieldsun

72 characters

=CHAR(INDEX(ROW($48:$109)+ROUND(INT(ROW($16:$77)/26)*6.6,0),RAND()*62+1))


Do you think asdf1001 is the winner？

 

[PA HS Teacher]

CHAR(LARGE({0,33,65}+ROW($32:$57),INT(RAND()*62)+1))

Very Clever!

The way this is set up, it doesn't matter that the resulting array from
{0,33,65}+ROW($32:$57) contains numbers that do not refer desired characters, because these numbers are not in the Top 62.


I'm impressed!

Good Job! (though we have to keep the $ if we are to paste the formula down, however since that was not a requirement, for the purposes of Erik's contest, I would unofficially (Erik's deadline has past) declare

={CHAR(LARGE({0,33,65}+ROW($32:$57),INT(RAND()*62)+1))}

the winner at 50 characters.

 

[apolloh]

The competition post abount this topic at the Excelhome Competition Forum.
http://club.excelhome.net/dispbbs.asp?boardid=102&id=112049

 

[wintercom]

i've found the answer,mayge it's shottest on the world,but it's pity that it's not make out by myself but by others.

the answer is :=CHAR(LARGE({0,33,65}+ROW(32:57),INT(RAND()*62)+1))

 

[tusharm]

Why? To both items! {grin}

Isn't is smarter to generate a solution that is obvious, transparent, simple to understand, and easy to maintain?

In fact, I suspect (but am far from sure since speed is the last thing I worry about) that an added bonus of the below page is that it is faster than any shorter array formula.

Random password
http://www.tushar-mehta.com/excel/tips/rand_alphanum.html

In fact, I would argue that even the formula on that page is not really transparent. For that, use
=IF(A1<10,A1,IF(A1<36,CHAR(A1-10+CODE("A")),CHAR(A1-36+CODE("a"))))

{snip}

the one who generates the shortest formula, may launch the next formulachallenge
{snip}