Randomization in spreadsheets




The randomization function

The function RAND() generate a random number between 0 and 1, including both (the interval [0;1], if you prefer this notation). The function does not require any variables, and a each update of the sheet, the randomization will generate a new number. If you need a random number from a different interval, you use a workaround as shown below.

For Microsoft Excel it looks like this:
Random value generated in an Excel spreadsheet.

For OpenOffice Calc it looks like this:
Random value generated in an Calc spreadsheet.





Randomization in other intervals

The function RAND() itself cannot generate random values for other intervals than [0;1], but using a small workaround, you can easily generate random values for any interval you may need. What you do in practice is multiplying the generated random number with the size of the interval and add the lowest value in the interval. If we write the minimum and maximum values in cells C2 and C3, you can calculate the random number for the interval in cell C4.

For Microsoft Excel it looks like this:
Random value generated in a specified interval in an Excel spreadsheet.

For OpenOffice Calc it looks like this:
Random value generated in a specified interval in a Calc spreadsheet.


The function generates a decimal number, as show on the images. If you need an integer, and often this is what you will need, you need to round the number, e.g. using ROUND(), so the full code for the example shown above will be =ROUND(RAND()*(C3-C2)+C2). Spreadsheets have several functions for rounding numbers, so ROUND() may not always be the optimal solution for something like this. These will be shown on a different page about rounding numbers.





Randomized selection of cells

One of the randomization functions that would make sense to have, would be the random selection of a cell from a selection of cells. Currently no such function exist. If you need a random selection like that, you need to use an IF() function, where you start by random selection of an integer, as shown above, followed by an IF() function connecting the value with a cell, e.g. if the random value is 1, you choose one cell and if the random number is 2, another cell is chosen, and so forth. The IF() function will be covered on a separate page.