FORMULA CHALLENGE shortest wins !

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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 :biggrin:

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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
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! :LOL:
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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)

Here's my test to make sure each upper case letter, lower case letter, and number occur with approximately the same frequencly.
Book6
ABCDEFGHI
1K0.00% Average Occurence of Upper Case Letters1.631%+/-0.112%
2R0.00% Average Occurence of Upper Case Letters1.602%+/-0.137%
3X0.00% Average Occurrence of Numbers1.594%+/-0.106%
4f0.00% 
5S0.00% 
600.00% 
760.00% 
8Z0.00% 
9r0.00% 
10u0.00% 
Sheet1
 

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
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 ---

Hi PA HS Teacher,

You can make this even more accurate by...

Code:
=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

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
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.

Code:
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

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Good call J,

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

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

malcom

Active Member
Joined
May 2, 2005
Messages
483
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

Well-known Member
Joined
Dec 2, 2002
Messages
1,947
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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.
 

Similar threads

Forum statistics

Threads
1,186,178
Messages
5,956,392
Members
438,249
Latest member
georgebasalic3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top