Hi! I am adapting a hangman game for my literacy students. The wordbank is
stored in a different sheet to the game, arranged in columns by the length of
the words. So for example, 5-letter words would be stored in column A,
6-letter ones in column B etc€¦ The students can either choose how many
letters there are in the word, or this can be randomly chosen. If so, a
column will be randomly selected, and a word is then randomly selected from
this column. Selecting the word is not a problem, but I am having difficulty
randomly selecting a column, because though random, I want the probabilities
of the columns being chosen to be relative to the number of entries in it,
i.e. I want the random process for selecting a column to be weighted.

I could have simply selected a column at random, with each column having an
equal chance of being picked:
=INT(([Number of columns]*RAND())+1)
However, this means that words appearing in a column with very few entries
will occur far more often than words in a column ten times the size, for
example.

The probabilities for each column being picked need to be weighted, as in
the following example:
The percentage shows the percentage of the whole wordbank that appears in
that column, the decimals represent the probability with which Id like that
column to be randomly chosen.

Entries in Column A 1 10% 0.1
Entries in Column B 2 20% 0.2
Entries in Column C 3 30% 0.3
Entries in Column D 4 40% 0.4

TOTAL 10 100% 1

So for example, Id like column A to be picked at random 10% of the time,
column B 20% etc€¦

Does anybody know how to weight the RAND function in this way please? Id be
really grateful for any help.
Many thanks in advance, Neil Goldwasser