Formula to return random string from list of strings?

Question

I want to display random courses (MBA, MSc) in OpenOffice Calc. I tried:

=RANDBETWEEN('MBA', 'MSc')  

and

=RAND('MBA', 'MSc')`  

but they don't work as desired.

Answer 1

In OpenOffice Calc, the RAND function returns a value between 0 and 1 - so you will have to combine different formulas to get a random selection from two text values. The following steps are needed:

• round the result of rand to an integer;
• based on that integer, select from list.

Try the following formula:

=CHOOSE(ROUND(RAND()+1);"MBA";"MSc")

or split up on different lines:

=CHOOSE(
    ROUND(
        RAND()+1
    );
    "MBA";
    "MSc"
)

Depending on you localization, you max have to replace the argument separators ; by :.

Explanation:

• the CHOOSE formula chooses from a list of values; the selection is based on the first argument (here: the rounded random value);
• the ROUND formula rounds the decimal to integer;
• RAND() + 1 makes sure that the resulting random value is either 1 or 2.

Answer 2

I'm not a user with a deep understanding of spreadsheets, but I thought this was an interesting question. I wanted to play around with an example with more than two choices and tried an exercise with six choices.

The OpenOffice wiki for the RAND function says...

RAND()*(b-a) + a

returns a random real number between a and b. 

Since the CHOOSE function needed integers 1 to 6 to make the 6 choices, RAND would need to output numbers from 1 to 6, I let a=1 and b=6.

This was tested,

=CHOOSE(ROUND(5*RAND()+1);"Business";"Science";"Art";"History";"Math";"Law")

That output a random selection of the six courses, but I found the six choices did not have equal chances of selection. Business and Law had a 1 in 10 chance of being selected and Science, Art, History, and Math had a 2 in 10 chance of being selected.

=CHOOSE(ROUNDUP(6*RAND()+0.00001);"Business";"Science";"Art";"History";"Math";"Law")

Seems to give all six courses a practically equal chance for selection.