< Previous lesson | Overview | Next lesson >


How to create and use random numbers

Sometimes you might want to use random numbers. "Why on earth would I do that?", you might ask. I'll try to convince you to continue reading this article with a few examples...

  • If you want to try out a new spreadsheet, with a lot of formulas and formatting, and you need to fill it with numbers, any numbers, just to see if the mechanics of the spreadsheet works, you can use random numbers.
  • If you want to select a random number for testing purposes, you can use random numbers.
  • If you have a list of words in a foreign language you want to practice, and want to be tested in the words randomly, you can use random numbers.
  • If you want to test different scenarios, you can use random numbers.

I have good news for you, my friend -- this is one of the easiest formulas to use :-)

The syntax is as follows:
  • =RAND()

That's it. There is actually nothing to put inside the parenthesis.

You will get a new value each time you force a recalculation of the spreadsheet or you enter any new calculation in the spreadsheet.

Go to the next step to learn how to actually use it.

Typing =RAND() into any cell will give you a random number between 0 and 1.

OK, say you want to vary the numbers between 0 and 1000, not just 1, what to you do then? Yes, you guessed it, you can multiply the formula with 1000. This way, since the result of the formula =RAND() is between 0 and 1, you will get a random number between 0 and 1000. For example:
  • =RAND()*1000
Using the same procedure; if you want the number to max out at 1 000 000, just mulitiply by 1 000 000, like this:
  • =RAND()*1000000

It's not always very realistic that you'll need any number from 0 and upwards, for example if you want random ages of persons, but only adults.

In many countries people are regarded as adults at the age of 18, so randomizing all numbers from 0 to, say maximum 100, using this would not suffice:
  • =RAND()*100

Since you require the person to be adult, it simply wont work, because you risk ending up with both 3 and 7!

Luckily it's not too difficult.
  • =RAND()*([max]-[min])+min

In this particulare example:
  • =RAND()*(100-18)+18

What happens here, is that you make sure that each value is at least 18, by adding 18 to any number you get. In addition you limit the maximum value by multiplying the result with 82 (100-18). Since we're adding 18 at the end of the formula, we make sure the random number, multiplied by 82, max out at 100.

Please experiment a bit, until you get the hang of the logic.