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:
For OpenOffice Calc it looks like this:
For Microsoft Excel it looks like this:
For OpenOffice Calc it looks like this:
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:
For OpenOffice Calc it looks like this:
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.
For Microsoft Excel it looks like this:
For OpenOffice Calc it looks like this:
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.
Excel function names in various languages
For some reason Microsoft has decided to have localized function names, so for non-English versions of Excel, you need to use the translated function names. This is as far as I have gotten, finding the localized function names for this chapter:
| Catalan | ALEAT() |
| Czech | NÁHČÍSLO() |
| Danish | SLUMP() |
| Dutch | ASELECT() |
| English | RAND() |
| Finnish | SATUNNAISLUKU() |
| French | ALEA() |
| Galician | ALEATORIO() |
| German | ZUFALLSZAHL() |
| Hungarian | VÉL() |
| Italian | CASUALE() |
| Norwegian | TILFELDIG() |
| Polish | LOS() |
| Portuguese (BR) | ALEATÓRIO() |
| Portuguese (PT) | ALEATÓRIO() |
| Russian | СЛЧИС() |
| Spanish | ALEATORIO() |
| Swedish | SLUMP() |
| Turkish | S_SAYI_ÜRET() |
| Catalan | ARRODONEIX() |
| Czech | ZAOKROUHLIT() |
| Danish | AFRUND() |
| Dutch | AFRONDEN() |
| English | ROUND() |
| Finnish | PYÖRISTÄ() |
| French | ARRONDI() |
| Galician | ARREDONDAR() |
| German | RUNDEN() |
| Hungarian | KEREKÍTÉS() |
| Italian | ARROTONDA() |
| Norwegian | AVRUND() |
| Polish | ZAOKR() |
| Portuguese (BR) | ARRED() |
| Portuguese (PT) | ARRED() |
| Russian | ОКРУГЛ() |
| Spanish | REDONDEAR() |
| Swedish | AVRUNDA() |
| Turkish | YUVARLA() |