FORMULA CHALLENGE shortest wins !

Here goes:
=CHAR(CHOOSE(ROUND(RAND()*2,0)+1,RAND()*10+48,RAND()*25+65,RAND()*25+97))

Now we'll wait for Aladin's response!

Hi, gurus, guys and gals.

This wasn't really a success
Or is there someone who forgot to post ? :wink:

Thank you, Barrie,
Your formula doesn't return "z" or "Z" and the numbers are appearing more then the letters
see rule 2. acurate = all characters must occur about as much as the others when formula is tested

if nobody takes anymore, this thread is dead
nevermind excel is alive

kind regards,
Erik

Here's a slightly simpler version of my formula from the original post. (for anyone watching, solutions are not supposed to require the analysis tool pack.)


Here's my test to make sure each upper case letter, lower case letter, and number occur with approximately the same frequencly.

PA HS Teacher said:

Here's a slightly simpler version of my formula from the original post. (for anyone watching, solutions are not supposed to require the analysis tool pack.)

Code:

=CHAR(CHOOSE(MATCH(RAND(),{0,0.16,0.58}),
INT((10)*RAND()+47),
INT((26)*RAND()+64),
INT((26)*RAND()+96))+1)

--- snip ---

Click to expand...

Hi PA HS Teacher,

You can make this even more accurate by...


Obviously this will not change the final result much, unless there is a large sample size.

Hi,

Not is the true spirit of the challenge (not a native Excel solution), but here is a UDF which gives you the option of specifying the length of the string to be returned.

Good call J,

And while I'm thinking about it, this is even a little shorter,

all four formulas produce less occurence for each number compared to each letter.. but the closest to the requirement is Barrie Davidson's code.. but, there was a little mistake..
it should have been...

=CHAR(CHOOSE(ROUND(RAND()*2,0)+1,RAND()*10+48,RAND()*26+65,RAND()*26+97))

erik.van.geit,
is Barrie Davidson the winner?

Barrie Davidson,
where's ur challenge?ur suppose to give the next challenge..

but i really wonder what is the so called shortest formula.. is it
the number of functions used?
the number of characters in the cell?
the number of keys to press?

If the shortest formula is refer to number of function used, then the following formula used only 2 functions :

=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789",RANDBETWEEN(1,62),1)

Regards

all four formulas produce less occurence for each number compared to each letter

Click to expand...

Actually,
My formula ensures that each character appears equally. I posted a test that shows this is true. All the other formulas pick a number from 1 to 3.
1 - random upper case letter
2 - random lower case letter
3 - random number

This logic is faulty because there are more letters than numbers. I used the Match function to ensure that numbers occur 10/62 of the time, upper case letter occurs 26/62 of the time, and lower cast letters appear 26/32 of the ti me. So, far my approach is the only formula approach that meets all of the criteria, including not requiring the analysis tool pack. (randbetwee() requires the analysis tool pack)

Bosco's formula can be modified to meet the rules:
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789",INT(63*RAND()),1)

Bosco's formula is 86 characters long, where as mine is 89 characters.
Well Done Bosco.

[Edit]
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789",INT(63*RAND()+1 ),1)

Thank you for catching the + 1 Barry, Looks like Bosco's formula is still shorter by 1 character.