Google Sheets: RANDBETWEEN
The Standard RANDBETWEEN
We can achieve a random range of numbers in Google Sheets by using the RANDBETWEEN formula. The formula is quite simple to use. Just add your start range and your end range:
=RANDBETWEEN(start range, end range)
For example, if I want to get a random number between zero(o) and 60 I would do the following:
=RANDBETWEEN(0,60)
The result will provide a whole random number somewhere between these two and including these numbers.
If I were to repeat this formula over a reasonable sample, say 300 times, we should see a fairly even spread of numbers occurring a roughly the same frequency, just like in the graph below:
But what if we wanted to have fewer random numbers in our list at the lower or upper end or on both ends?
Random Range with Bias Towards Middle Numbers or One End
To achieve this we can nest another RANDBETWEEN inside the first RANDBETWEEN.
Using our sample of 300, take a look at how we can achieve his.
Random Set with Fewer Lower Number
To create a data set with fewer lower numbers, we put the nested RANDBETWEEN in the start range of the main RANDBETWEEN. Let’s say we want fewer numbers to occur between 0 and 20 from our greater list of 0-60.
=RANDBETWEEN(RANDBETWEEN(0,20),60)
As you can see, there is a stark drop off in frequency between 0 and 20.
Random Set with Fewer Upper Numbers
To create a data set with fewer upper numbers, we put the nested RANDBETWEEN in the end range of the main RANDBETWEEN. Let’s say we want fewer numbers to occur between 40 and 60 from our greater list of 0-60.
=RANDBETWEEN(0,RANDBETWEEN(40,60))
As you can see, there is a stark drop off in frequency between 40 and 60.
Random Set with Fewer Upper and Lower Numbers
To create a data set with fewer upper and lower numbers, we put the nested RANDBETWEEN in the start and end range of the main RANDBETWEEN. Let’s say we want fewer numbers to occur between 0 and 20 and, 40 and 60 from our greater list of 0-60.
=RANDBETWEEN(RANDBETWEEN(0,20),RANDBETWEEN(40,60))
As you can see there is a stark drop off at both ends now.
Application – An Example
My universities deanship is creating its own Learning Management System. They wanted to test the system with a random set of grades for their coursework out of 60. I wanted to give them a set of grades that more reasonably matched what the students would get over the quarter’s coursework.
My sample is 649 students. I know that the bulk of the grades sits between 25/60 and 50/60. So I created the following formula and iterated it down each row for the total of the sample size:
=RANDBETWEEN(RANDBETWEEN(0,25),RANDBETWEEN(50,60))
This gave the following results:
As you can see, the sample as fewer grades that are less than 25 and greater than 50.
Of course, this is no perfect. Ideally, you would want to see a curve rather than a linear representation with skewed ends, but it will suffice for my system testing purposes.
~Yagi