Random generation in excel.
Today we will discuss the RAND and RANDBETWEEN formulas.
The random generators in excel.
The RAND formula gives us a random number between 0 and 1
The RANDBETWEEN formula gives us a random number between what we specify as bottom and top values.
The RANDBETWEEN will give us back integers.
Why would I need random numbers in excel? Where would I use this?
Let’s say you cheating with your reports… Erm…
Cosmetically making up numbers. But you don’t want your KPI to be exactly 100% do you?
What do you do then? You making up numbers that look random.
This will give you very nice scores on your report. Like this:
Notice that we asked for a random number between 10 and 50, then we divide this number back by 10. This is how you can get RANDBETWEEN decimals.These numbers will be added back on to our initial 95.
Where else would I use the RAND and RANDBETWEEN formulas?
Sometimes we need to test formulas and we need test data. I found it the easiest with RANDBETWEEN to generate my datasets.
Things to note:
- RAND and RANDBETWEEN formulas are re-calculating each time something changes in your workbook.
To fix them in place use Copy then Paste Special -> Paste as values in place. This will “value out” your datasets.
- If you just need one random value you can type your formula in to the formula bar, then hit F9.
That will fix the value in the formula bar. Hit Enter and you fixed it to your cell.