Random Number Generator [Calc]

Amazon affiliate: OpenOffice.org 3.0 for Beginners


A friend of mine is familiar with working in Lotus 1-2-3 and has asked me for some assistance in converting to Excel or OpenOffice Calc. Of course I chose to post my Calc solutions here. :-)

The formula for random number generation is quite easy.

=RAND()

This will get you a random number between zero and 1 (2 decimal places is the default format).

To generate a random number between two values, the formula is modified slightly to:

=RAND()*(b-a) + a
Where b is the higher value and a is the lower value.

Update: You can also use RANDBETWEEN (thanks to Jason):

=RANDBETWEEN(a; b)

For example, to generate a random number between 1 and 100, you would use the following formulae:

=RAND()*(100-1)+1    or
=RANDBETWEEN(1;100)

If you wish this to be a whole number (no decimal places), you can do this in one of two ways.

By Formula

You can modify the formula to include the ROUND function:

=ROUND(RAND()*(100-1)+1)

By Format

You can use the Delete Decimal Place icon on the toolbar and reduce the number of decimal places.

Screenshot-2

No related content found.

Additional Resources

Comments

  1. Hi,

    I am emailing from the UK. Happy New Year!

    I use Lotus 123 like your friend and do basic macros but have a problem.

    I was wondering if you could help.

    I want to generate a LIST of random numbers NOT JUST ONE.

    For example a column of 100 numbers requesting a random number between 1 and 100.

    So I end up with a list of higgledy-piggledy containing all numbers from 1 to 100.

    I don’t know what commands to put in the Lotus 123 macro to acheive this.

    Can you help or suggest anything.

    Many thanks.

    Best wishes

    Les

    • stephanie says:

      Hi Les,

      I’m not usually a Lotus Gal… but if you put a random number calculator in each cell, doesn’t that work?

      Steph

  2. Wouldn’t randbetween() provide the same results as =RAND()*(b-a) + a?

  3. Jason – yes thanks for the update. This was originally posted in 2009, and my understanding is that RANDBETWEEN had some issues with 64-bit systems at this time, which is why I included this method.

    As you have correctly pointed out, both are now documented in the OOo wiki: http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_RANDBETWEEN_function

    Thanks again!

  4. Ifan Jones says:

    If you have a column of random generated numbers, how can you ensure that none of the numbers are duplicated?

    For example, in columns A1 to A200 the function =randbetween(2500;3500) is inserted into each cell, how can we ensure it does not randomly create the same number in different cells?

  5. Dear Ifan Jones,

    There is a forum post here which has an interesting solution to testing for uniqueness. If you go to the 5th post from ‘ken johnson’ you will see a link to a spreadsheet. The poster of this refers to a formula in B1, which is used to test random number uniqueness, where you would press Ctrl+Shift+F9 until B1 shows “OK”.

    This formula, based on your requirements of having unique values for cells A1 to A200, could be amended and placed in your B1 as:
    =IF(200-SUMPRODUCT(1/COUNTIF(A1:A200;A1:A200))<>0;”Press Ctrl+Shift+F9″;”OK”)

    I hope this assists you.

    Regards,
    Stephanie

Trackbacks

  1. […] an update to my previous post on generating random numbers, I have included a reference to the function […]

Speak Your Mind

*