In this blog I am going to introduce two things I am ashamed to say I have only just learned. It just goes to show that even after all these years of using and learning Excel spreadsheeting, new things can still appear.
I am going to talk about
- · =RAND*(b-a)+a
- · =RAND() and the F9 key
The formula =RAND*(b-a)+a is used instead of =RANDBETWEEN(…). So if you want to generate random numbers from, say, 7 and 11 then a = 11 and b = 7. That is, a>b.
The following screenshot shows you the effect. In this screenshot I have generated the random numbers in their own cells using =RAND() and then used, for example, =A1*(b-a)+a to finish it off:
Press the F9 key to refresh this volatile function and see that it does give you randbetween using rand. Please note, the answers are NOT integers, use =RANDBETWEEN(…) for that.
That’s is, but here is a screenshot of a chart I prepared so that by pressing the F9 you can see how random your chosen numbers are. In my case I created a table with two sets of 30 random numbers, X and Y.
=RAND() and the F9 key
I really am surprised that I did not know that this utility existed. Imagine you want a random number that is not volatile … you just want to generate such a number and don’t need it to refresh every time you hit F9 or Enter.
Put your cursor in the cell where you want a random number to appear. Then in the FORMULA BAR and NOT in the cell, type =RAND() and then press the F9 key. That’s it and in your chosen cell you will have a random number and not =RAND().
This also works with =RAND()*n where ‘n’ is any number