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

 

[Andrew Fergus]

Hi Erik

Very nice try, except hady mentioned the formula was to be entered into cell B3 - I took that to mean no 'helper' cells. I think my original formula has a bug or two, which has been removed from this (even longer) version. The difference now is that it won't recalculate once it has found a solution and I'm still assuming all of the words are the same length.

{=IF(LEN(B3)<SUM(LEN(A1:A9)), IF(ISERROR(FIND(RIGHT(B3, LEN(A1)), LEFT(B3, LEN(B3)-LEN(A1)))), B3&INDEX(A1:A9, INT(RAND()*10)), LEFT(B3, LEN(B3)-LEN(A1))), IF(LEFT(B3)=0, RIGHT(B3, LEN(B3)-1), RIGHT(B3,SUM(LEN(A1:A9)))))}

....same as before, circular reference, array formula, in cell B3....

Cheers, Andrew

 

[hady]

Hi Erik

Very nice try, except hady mentioned the formula was to be entered into cell B3 - I took that to mean no 'helper' cells. I think my original formula has a bug or two, which has been removed from this (even longer) version. The difference now is that it won't recalculate once it has found a solution and I'm still assuming all of the words are the same length.

=IF(LEN(B3)<SUM(LEN(A1:A9)), IF(ISERROR(FIND(RIGHT(B3, LEN(A1)), LEFT(B3, LEN(B3)-LEN(A1)))), B3&INDEX(A1:A9, INT(RAND()*10)), LEFT(B3, LEN(B3)-LEN(A1))), IF(LEFT(B3)=0, RIGHT(B3, LEN(B3)-1), RIGHT(B3,SUM(LEN(A1:A9)))))

Cheers, Andrew

You are correct Andrew, the formula has to be done in one cell and no other helper cells or dummy. :wink:
SO far your formula is about my expectation, let see what others have
btw Andrew if you assume they are the same length why are you using array like sum(len(A1:A9)) and not using len(A1)*9, means you don't need CSE but enter

hady

 

[Andrew Fergus]

Good point about the *9 and CSE. Thanks for that. I guess that is what happens when I have a number of iterations on the same formula - bits get left behind.

When I saw this question I immediately remembered your post about solving hatman's palindrome question, so thanks for that! However, in a real-world situation I think I would have chosen the UDF option.

The advantage of using helper cells (like in Erik's example) is that the length of the words doesn't matter - at this point I have no idea how to get around the question of the length of the item just added. Do you have any thoughts on that?

Cheers
Andrew

new formula :

=IF(LEN(B3)<LEN(A1)*9, IF(ISERROR(FIND(RIGHT(B3, LEN(A1)), LEFT(B3, LEN(B3)-LEN(A1)))), B3&INDEX(A1:A9, INT(RAND()*10)), LEFT(B3, LEN(B3)-LEN(A1))), IF(LEFT(B3)=0, RIGHT(B3, LEN(B3)-1), RIGHT(B3,LEN(A1)*9)))

btw Andrew if you assume they are the same length why are you using array like sum(len(A1:A9)) and not using len(A1)*9, means you don't need CSE but enter

 

[erik.van.geit]

a beauty, Andrew

nice challenge as long as everubody is aware that this is just for fun and learning a UDF or formulasystem is more userfriendly (no pressing F9)

perhaps you could add a sheet-change-event with SendKeys in a loop till B3 get's it's required length: then it would be userfriendly
but OK this was not the purpose

have a nice time playing!
Erik

 

[Andrew Fergus]

Hi Erik

It still isn't right - because occasionally it repeats a value in the last position. Slight variation :

=IF(LEN(B3)<=LEN(A1)*9, IF(ISERROR(FIND(RIGHT(B3, LEN(A1)), LEFT(B3, LEN(B3)-LEN(A1)))), B3&INDEX(A1:A9, INT(RAND()*10)), LEFT(B3, LEN(B3)-LEN(A1))), IF(LEFT(B3)="0", RIGHT(B3,LEN(B3)-1), LEFT(B3,LEN(A1)*9)))

This one never exits the loop so it keeps trying to add another word even after it has finished - so it still isn't right. That's about the best I can do so I'm stopping now - it's too hard!

Cheers, Andrew

 

[smiler]

This is as close as i can get it :x

i set iterations to 1000 - copy the formula into cell b3, occasionaly you will get numbers instead of the answer, press f9 and the recalculate will give the result, once you have a result, click cell b3 and re-enter the formula

best i can come up with, i am no expert, maybe one f you guys can get it to work fully automated

=IF(AND(NOT(ISTEXT(B3)),NOT(ISERROR(B3))),    IF(LEN(B3)>=COUNTA(A:A)+1,   INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)+1),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)), 1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)-1),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)-2),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)-3),1,1))&INDEX(A1:A100,MID(RIGHT(B3, COUNTA(A:A)-4),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)-5),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)-6),1,1))&INDEX(A1:A100, MID(RIGHT(B3,COUNTA(A:A)-7),1,1)),     IF(OR(LEN(B3)<2, ISERROR(FIND(RIGHT(B3,1),LEFT(B3,LEN(B3)-1)))),   VALUE(B3&(1+(INT(RAND()*COUNTA(A:A))))),VALUE(LEFT(B3, LEN(B3)-1)&1+INT(RAND()*COUNTA(A:A))))),   IF(ISERROR(B3),"0",B3))

edited to space code better
S/\/\iler :wink:

 

[hady]

This is as close as i can get it :x

i set iterations to 1000 - copy the formula into cell b3, occasionaly you will get numbers instead of the answer, press f9 and the recalculate will give the result, once you have a result, click cell b3 and re-enter the formula

best i can come up with, i am no expert, maybe one f you guys can get it to work fully automated

=IF(AND(NOT(ISTEXT(B3)),NOT(ISERROR(B3))),IF(LEN(B3)>=COUNTA(A:A)+1,INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)+1),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)-1),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)-2),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)-3),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)-4),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)-5),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)-6),1,1))&INDEX(A1:A100,MID(RIGHT(B3,COUNTA(A:A)-7),1,1)),IF(OR(LEN(B3)<2,ISERROR(FIND(RIGHT(B3,1),LEFT(B3,LEN(B3)-1)))),VALUE(B3&(1+(INT(RAND()*COUNTA(A:A))))),VALUE(LEFT(B3,LEN(B3)-1)&1+INT(RAND()*COUNTA(A:A))))),IF(ISERROR(B3),"0",B3))

S/\/\iler :wink:

678 characters in formula, good try
I haven't test this is real random or set random

 

[smiler]

this should be real random (or as random as Rand() will give)

one litle bug, when you enter this, if the rand() produces a value that it already has it will stop and you need to press f9 to recalculate, generally only have to do this once to get the answer

 

[smiler]

Voila!! - fully working, fully automated

have removed the count functions (tried to future proof in the original so you could have more than nine words, but this would never have worked as 10 would have been seen as 1 and 0, so have reset all counts to be a static 9

=IF(NOT(ISTEXT(B3)),IF(LEN(B3)=10,INDEX(A1:A100,MID(RIGHT(B3,10),  1,1))&INDEX(A1:A100,MID(RIGHT(B3,9), 1,1))&INDEX(A1:A100,MID(RIGHT(B3,8),1,1))&INDEX(A1:A100,MID(RIGHT(B3,  7),1,1))&INDEX(A1:A100,MID(RIGHT(B3,6),1,1))&INDEX(A1:A100,MID(RIGHT(B3, 5),1,  1))&INDEX(A1:A100,MID(RIGHT(B3,4),1,1))&INDEX(A1:A100,MID(RIGHT(B3,3),1,1))&INDEX(A1:A100, MID(RIGHT(B3,2),1,1)),IF(ISERROR(FIND(RIGHT(B3,  1),LEFT(B3,LEN(B3)-1))),VALUE(B3&(1+(INT(RAND()*9)))),VALUE(LEFT(B3, LEN(B3)-2)&1+INT(RAND()*9)))),1+INT(RAND()*9))

sorry if this is not paginated correctly, not sure how putting spaces in would help

S/\/\iler

 

[erik.van.geit]

wow, nice try,
I can only dream about creating such formula
and it doesn't matter how long the items are !!

  A         B                                             
1 a                                                       
2 bb                                                      
3 ccc       gggggggacccffffffbbeeeeehhhhhhhhiiiiiiiiidddd 
4 dddd                                                    
5 eeeee                                                   
6 ffffff                                                  
7 ggggggg                                                 
8 hhhhhhhh                                                
9 iiiiiiiii                                               

test
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
B3    =IF(NOT(ISTEXT(B3)),IF(LEN(B3)=10,INDEX(A1:A100,MID(RIGHT(B3,10),  1,1))&INDEX(A1:A100,MID(RIGHT(B3,9), 1,1))&INDEX(A1:A100,MID(RIGHT(B3,8),1,1))&INDEX(A1:A100,MID(RIGHT(B3,  7),1,1))&INDEX(A1:A100,MID(RIGHT(B3,6),1,1))&INDEX(A1:A100,MID(RIGHT(B3, 5),1,  1))&INDEX(A1:A100,MID(RIGHT(B3,4),1,1))&INDEX(A1:A100,MID(RIGHT(B3,3),1,1))&INDEX(A1:A100, MID(RIGHT(B3,2),1,1)),IF(ISERROR(FIND(RIGHT(B3,  1),LEFT(B3,LEN(B3)-1))),VALUE(B3&(1+(INT(RAND()*9)))),VALUE(LEFT(B3, LEN(B3)-2)&1+INT(RAND()*9)))),1+INT(RAND()*9))

[Table-It] version 06 by Erik Van Geit

here a test to check how much times there are numbers coming up (instead of the items)

Sub test()
Dim i As Long
Dim l As Integer

l = Evaluate("=SUM(LEN(A1:A9))")

    For i = 1 To 1000
    Calculate
    If Len(Range("B3")) <> l Then Range("C" & Rows.Count).End(xlUp)(2) = Range("B3")
    Next i

End Sub