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.