As part of some work I am doing with a colleague and as part of my normal training materials, I often explain how and why I use the RANDBETWEEN() function to generate specimen numbers when either I don’t have real values or I cannot use real values for confidentiality reasons. (See my other new page of today, COUNTIF and COUNTIFS, here

The purpose of this page is to illustrate how to use RAND() to simulate RANDBETWEEN(). There is a difference between these two approaches although they both give the same overall results. The difference is that RAND() can provide you with values such as 123.456, 654.321, 46.98 … that is, numbers and fractions of numbers. RANDBETWEEN() on the other hand only ever returns integers. Having said that, we can make RANDBETWEEN() give us a value such as 123.456 in this way:

=RANDBETWEEN(0,200000)/1000

That is, generate a random number between 0 and 200,000 and divide the answer by 1000 to give, for example, 123,456/1000 = 123.456.

RAND() generates a random number between 0 and 1. However, I might want a distribution of random numbers taken from a specified range: such as 0 to 200,000 in which the values have to have four places of decimals. Here is how RAND() can be programmed to do that:

=A1*(b-a)+a

where

a and b are the values at the top and bottom of your range of values, your RANDBETWEEN extreme values

a must be greater than b

The following range starts with the letter ‘a’ in cell C24

a 200
b 100
a b RANDBETWEEN 1
=RAND() =RAND() =C28*($D$25-$D$24)+$D$24
=RAND() =RAND() =C29*($D$25-$D$24)+$D$24

Look at that carefully and work on it in the attached Excel work book.

Take a look at cell A11 in that work sheet as it shows you how to use RAND() to generate a random number BUT Excel shows that number as a static value and not as a volatile RAND() statement … go and see to understand if you’ve never seen this before!

Download the Excel file rand_randbetween

There are several features built into this apparently simple file that you need to explore … make it your job to find them all!!

All feedback warmly received!

Duncan Williamson

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: