Randomly select from alphabet based on letter frequency distribution.

I want to randomly select letters from this distribution (to make random words) but I want the more frequent letters in the distribution to be selected more often so that the word I form have the same distribution. I have the feeling there is a simple solution to this but I couldn't see anything other than the frequency function that I thought I might be able to use. I could populate an array with letters present in numbers according to their frequency and then randomly select from it but is there a more elegant solution?

Neil

E 11.1607% 56.88 M 3.0129% 15.36
A 8.4966% 43.31 H 3.0034% 15.31
R 7.5809% 38.64 G 2.4705% 12.59
I 7.5448% 38.45 B 2.0720% 10.56
O 7.1635% 36.51 F 1.8121% 9.24
T 6.9509% 35.43 Y 1.7779% 9.06
N 6.6544% 33.92 W 1.2899% 6.57
S 5.7351% 29.23 K 1.1016% 5.61
L 5.4893% 27.98 V 1.0074% 5.13
C 4.5388% 23.13 X 0.2902% 1.48
U 3.6308% 18.51 Z 0.2722% 1.39
D 3.3844% 17.25 J 0.1965% 1.00
P 3.1671% 16.14 Q 0.1962% (1)

Thank-you - that makes sense to me and has exactly the simplicity I thought it should.

I have a small problem , probably simple one - the call to RAND() inside VLOOKUP() (I think) is causing the functioncall to fail - the equation, once entered, is not replaced by a return value, it persisits as though it was pure text - I did get an error message saying that a value was not available - sounds as though there may be a lock-up or a loop somehow. I've had this before and muddled through but Haven't managed to get this working. Previsoulsy I think I simply moved the RAND() call to anothe cell and then included the cell reference int eh VLOOKUP call instead of a call to RAND() - unfortunately that hasn't helped to reslove it this time - any idea what I'm doing wrong?

Thank-you nontheless.
Neil

any idea what I'm doing wrong?

No clue; it works fine for me.

You can post a workbook ...

See attached file. Thanks. Perhaps my Excel version needs updating.
Neil

Your formula is =VLOOKUP(RAND(),$S$5:$T$30,2,FALSE)

You can't use the FALSE argument; see Help to understand why.

Originally Posted by shg

Your formula is =VLOOKUP(RAND(),$S$5:$T$30,2,FALSE)

You can't use the FALSE argument; see Help to understand why.

Stupid error on my part - it was 2am in the morning and I was tired however taking it out or replacing it with TRUE does not remove the problem for me. Are you saying it did for you?

Neil

Good thinking shg.

The problem is that cells are formatted as text. Change the first one to General, edit it (F2), press enter and then copy down.

Originally Posted by StephenR

Good thinking shg.

The problem is that cells are formatted as text. Change the first one to General, edit it (F2), press enter and then copy down.

Sorry about that! Thank-you both for posting. I'm really pleased with the way the sheet turned out - the randomly generated words do look like new strange words from the English language and not Polish. I hope that it will help improve the handwriting in my class - words are simply rushed during handwriting practice because the children (generally) know how to spell the words - this will slow them down allowing them to concentrate on their letter formation and joins - well, that's the plan.

Thanks again.
Neil

Interesting, glad it worked for you. StephenR and I are a tag team ...

shg does all the work and I try to sneak in at the end and take all the plaudits.

I'm pleased to think that VBA is being used to improve handwriting, my own is somewhat neglected from all this typing.