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.

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

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

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

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!

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?

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