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

 

[Barrie Davidson]

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!

 

[erik.van.geit]

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

 

[PA HS Teacher]

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.)

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

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

Book6
	A	B	C	D	E	F	G	H	I
1	K		0.00%			Average Occurence of Upper Case Letters	1.631%	+/-	0.112%
2	R		0.00%			Average Occurence of Upper Case Letters	1.602%	+/-	0.137%
3	X		0.00%			Average Occurrence of Numbers	1.594%	+/-	0.106%
4	f		0.00%
5	S		0.00%
6	0		0.00%
7	6		0.00%
8	Z		0.00%
9	r		0.00%
10	u		0.00%
Sheet1

 

[Jay Petrulis]

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.)

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

--- snip ---

Hi PA HS Teacher,

You can make this even more accurate by...

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

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

 

[Jay Petrulis]

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.

Function Random_Letters_or_Numbers( _
    Optional lngLength As Long = 1, _
    Optional IsStatic As Boolean = False)
    
    Dim arrOptions(1 To 62), _
        i As Long, _
        result
    
    If Not (IsStatic) Then Excel.Application.Volatile True
    Randomize
    
    For i = 1 To 62
        If i <= 10 Then
            arrOptions(i) = Chr(i + 47)
        ElseIf i < 37 Then
            arrOptions(i) = Chr(i + 54)
        Else
            arrOptions(i) = Chr(i + 60)
        End If
    Next i
    
    
    For i = 1 To Excel.Application.Max(1, lngLength)
        result = result & arrOptions(Int(Rnd * 62 + 1))
    Next i
    
    Random_Letters_or_Numbers = result
    End Function

 

[PA HS Teacher]

Good call J,

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

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

 

[malcom]

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?

 

[bosco_yip]

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

 

[PA HS Teacher]

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

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.