# FORMULA CHALLENGE shortest wins !

#### erik.van.geit

##### MrExcel MVP
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
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
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

##### MrExcel MVP
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

nevermind excel is alive

kind regards,
Erik

#### PA HS Teacher

##### Well-known Member
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
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
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
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
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
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
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.

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.

Replies
16
Views
1K

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.

### Which adblocker are you using?

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

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