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

 

[barry houdini]

=INT(63*RAND()) is not equivalent to

=RANDBETWEEN(1,62)

You need

=INT(62*RAND()+1)

 

[PA HS Teacher]

Actually,
When I delete the hard returns from my formula (which I just like to use for readability), the formula is 86 characters long, or two shorter than the formula using Mid.


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

 

[Scott Huish]

Although I agree a native function would probably be better, there is nothing in the rules that prohibits add-ins:

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 ?

 

[barry houdini]

As per Barrie's formula above, you don't need INT.....

=CHAR(CHOOSE(MATCH(RAND(),{0,10,36}/62),10*RAND()+48,26*RAND()+65,26*RAND()+97))

 

[PA HS Teacher]

Shorter still at 70 characters:

=CHAR(INDEX(RAND()*{10;26;26}+{48;65;97},MATCH(RAND(),{0,10,36}/62),1))

[edit] 68 Characters:

CHAR(INDEX(RAND()*{10;26;26}+{48;65;97},MATCH(RAND(),{0,10,36}/62)))

 

[barry houdini]

I was also looking for a shorter way - I didn't consider INDEX - very nice

...tiny bit shorter..

=CHAR(INDEX(RAND()*{10;26;26}+{48;65;97},MATCH(RAND(),{0,5,18}/31)))

:wink:

 

[bosco_yip]

A bit shorter :

=CHAR(LOOKUP(RAND(),{0,5,18}/31,RAND()*{10;26;26}+{48;65;97}))

 

[erik.van.geit]

Hi, guys,

I'm testing the formulas this way:
(if you want a copy of the workbook just email me)
copy 20000 times to sheet (

Sub paste_formula()
Range("formulas").Formula = ActiveCell.Formula
End Sub

generate a sheetcalculation just by copying the counts for 1 loop of each character to the general count
Range("results_once").Copy
Range("results_loops").PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd

Sub test()
Dim j As Integer
Dim loops As Integer

loops = Application.InputBox("how many loops of 20.000 formula-tests?" & Chr(10) & "MAX: 500", "LOOPS", "10")
If loops > 500 Then loops = 500

With Application
.ScreenUpdating = False
    For j = 1 To loops
    Application.StatusBar = "total loops to do :" & loops & " ... loops done:" & j - 1
    Range("results_once").Copy
    Range("results_loops").PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
    Next j
    Application.StatusBar = False
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub

part of the results using Teachers formula (68 characters "=" not counted)

formulachallenge.xls
	B	C	D	E	F	G	H	I	J	K
2	generate		items	appeared		after loops		tests	lowest	highest
3	W		a	331		80944	1,619%	5000000	79879	81540
4	B		b	342		79879	1,598%		1,598%	1,631%
5	9		c	358		80127	1,603%		difference
6	Z		d	307		80584	1,612%		1661
7	1		e	337		80569	1,611%		2,02%
8	s		f	315		80715	1,614%
9	3		g	316		80801	1,616%		ideal
10	N		h	321		81540	1,631%		1,613%
test

kind regards,
Erik

 

[erik.van.geit]

Hello, guys,

the original rules were

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 ?

after one week no valid answer was given, in fact there was only one answer
after a little bump from my side you were coming up with answers
so rule 3. was not applied
rule 4. was perhaps not a good one ?
5. jury ? if everybody agrees bosco_yip is the winner using 61 characters (=sign not counted) then we don't need a jury
I let run a macro to do 10.000.000 tests (500 - 20.000) and the result was OK, as you could all see looking to the formula

tests	   lowest	highest
10000000	160255	162047
           1,603%   1,620%

if nobody has an objection ...
bosco_yip, are you ready to invent a new formula-challenge?
What's the shortest formula to ...

kind regards,
Erik
(offline for a week starting 24 hours from now)

 

[UCSDKID]

How about this?? I think this should pass your test.
A little different thinking process.

=CHAR(IF(RAND()<10/62,RAND()*10+48,N(RAND()>.5)*32+RAND()*26+65))

64 characters