Random Number Generator in Excel, OpenOffice Calc & Google Sheet with 9 random generators to generate random integers, decimals, date, time, alphabets, names, teams, etc.
Moreover, Random Number Generator generates singular or multiple random numbers with or without duplicates.
In addition to that, the Random Name and Team Generator generates a random name from a given list. It also generates random teams from a given set of names.
Furthermore, it consists of the Random Date and Time Generator that helps you generate random dates and times between two given ranges.
Table of Contents
Download Random Number Generator (Excel, OpenOffice Calc & Google Sheet)
With the help of VBA coding and using Functions, we have created a simple and easy 9 different Random Generators.
Just download the file, insert the lower range and upper range and the template will automatically generate the random for you.
What are Random Numbers?
A random number is a number chosen as if by chance from some specified distribution such that the selection of a large set of these numbers reproduces the underlying distribution. Almost always, such numbers are also required to be independent, so that there are no correlations between successive numbers.
Source: www.mathworld.wolfarm.com.
A random number is chosen from a large set of numbers. Such numbers are also required to be independent so that there are no correlations between successive numbers.
Contents of Random Number Generator Template
This template consists of 9 Random Generators:
- Number Generator (With Duplicates)
- Integer Generator (Without Duplicates)
- Decimal Generator
- Date Generator
- Time Generator
- Alphabet Generator
- Name Generator
- Team Generator
- Lottery Ticket Winner Generator
Let us understand the functioning of each in detail.
Random Number Generator (With Duplicates)
This template generates a single Random number and a set of random numbers from a given range. Specify the lower range in cell D6 and upper range in cell F6. It will automatically generate random numbers for you between that range.
This section uses the RANDBETWEEN function to generate random numbers within a given range. The only limitation with the RANDBETWEEN function is that when applied to multiple cells it generates some duplicates numbers between that range.
You can use this to generate such numbers for kids to practice mathematical functions like addition, subtraction, multiplication, division, etc.
Every time you make changes to the sheet these random numbers will change. If you want to use those numbers, kindly copy and paste using “Paste Special” selecting the “Values” option to another location in the sheet.
Random Integer Generator (Without Duplicates)
This Random Number Generator displays multiple random numbers/integers without duplicates. It generates 100 random numbers.
In this section, it is necessary that the difference between the lower range and the upper range should be 100. Then only the generator works properly. Otherwise, it will display the following message:
Moreover, the template uses a VBA code to generate random numbers between a range. The code is explained at the end of the article so that you can use it according to your requirements.
Random Decimal Generator
Random Decimal Generator generates random decimal numbers between a range. Insert the lower limit and the upper limit to generate the decimal numbers.
This section uses the RANDBETWEEN function to generate random numbers within a given range. When applied to multiple cells it generates some duplicates numbers between that range.
Furthermore, if you want to use those numbers, kindly copy and paste using “Paste Special” selecting the “Values” option to another location in the sheet.
It uses the following formula:
=RANDBETWEEN($D$6*100,$F$6*100)/100
You can use this to generate such numbers for kids to practice mathematical functions.
Random Date Generator
Date Generator 5 different dates between a given range of dates. Insert the start date and end date. This section also uses RANDBETWEEN Function with DATEVALUE Function.
It uses the following formula:
=RANDBETWEEN(DATEVALUE(“1-Jan-21”),DATEVALUE(“15-Jan-21”))
Random Time Generator
Time Generator displays random time between the time range. Insert start time and end time and it will generate 5 different times between the range. This section uses the TIME VALUE Function with RAND Function.
It uses the following formula:
=TIMEVALUE(“12:00 AM”) + RAND() * (TIMEVALUE(“6:00 PM”) – TIMEVALUE(“12:00 AM”))
Random Alphabet Generator
Alphabet Generator display alphabets between a given range. Insert the start alphabet between A To Z and it will display the random alphabet for you.
It uses RANDBETWEEN Function with a combination of the CHAR Function and CODE Function. It uses the following formula:
=CHAR(RANDBETWEEN(CODE($D$22),CODE($F$22)))
Random Name Generator
Usually, this section is useful to a teacher or school to generate a random name from a given list of students. Insert names of the students or employees in a single column and apply the formula.
If the names are not in a single column it will not generate the random name and displays a #Value error.
This section uses RANDBETWEEN Function along with the INDEX Function. It uses the following formula:
=INDEX(B31:B42,RANDBETWEEN(1,12))
Random Team Generator
Team Generator generates a team from a list of names. Insert the names in a single column and apply the formula. As in the above generator, if the names are not in a single column it will not generate the random name and displays a #Value error.
This section uses RANDBETWEEN Function along with the INDEX Function. It uses the following formula:
=INDEX($B$31:$B$42,RANDBETWEEN(1,12))
Random Lottery Ticket Winner Generator
This section is similar to the above section. Insert the lottery number in series in the column and apply the formula. This section also uses RANDBETWEEN Function along with the INDEX Function.
VBA Code to Generate Random Numbers Without Duplicates
The Random Number Generator Without Duplicates uses the following VBA Code:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Public Sub generateRandNum() | |
| 'Define your variabiles | |
| lowerbound = Range("D22") | |
| upperbound = Range("F22") | |
| Set randomrange = Range("B24:F43") | |
| randomrange.ClearContents | |
| For Each rng1 In randomrange | |
| counter = counter + 1 | |
| Next | |
| If counter > upperbound – lowerbound + 1 Then | |
| MsgBox ("Number of cells > number of unique random numbers") | |
| Exit Sub | |
| End If | |
| For Each Rng In randomrange | |
| randnum = Int((upperbound – lowerbound + 1) * Rnd + lowerbound) | |
| Do While Application.WorksheetFunction.CountIf(randomrange, randnum) >= 1 | |
| randnum = Int((upperbound – lowerbound + 1) * Rnd + lowerbound) | |
| Loop | |
| Rng.Value = randnum | |
| Next | |
| End Sub |
Source: www.access-excel.tips
Frequently Asked Questions
There are two methods of generating random numbers: True-Random Method and Psuedo-Random Method. True random numbers use physical phenomena like atmospheric noise, thermal noise, and other quantum phenomena. It generates true random numbers. A pseudo-random method uses an algorithm for generating a sequence of numbers. Usually, computer-based random number generators always use the pseudo-random method. Moreover, the numbers generated by the pseudo-random method are not truly random.What are the different methods to generate random numbers?
What is True Random Method?
What is Pseudo-Random Method?
If you like this article, kindly share it on different social media platforms. So that your friends and colleagues can also benefit from the same. Sharing is Caring.
Moreover, send us your queries or suggestions in the comment section below. We will be more than happy to assist you.