EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel RANDBETWEEN in Excel

RANDBETWEEN in Excel

By Madhuri ThakurMadhuri Thakur

RANDBETWEEN in Excel

Excel RANDBETWEEN (Table of Contents)

  • Introduction to Excel RANDBETWEEN
  • How to Generate Random Numbers in Excel?

Introduction to Excel RANDBETWEEN

When we speak about Excel, there is a function called RANDBETWEEN, which helps you to generate pseudo-random numbers between a given range of numbers. These are the random numbers predicted with some mathematical algorithm in the backend. RANDBETWEEN is a volatile function in Excel. It will change the values generated every time we refresh/newly define the formula over the same range. Excel RANDBETWEEN function generates a single random number at one iteration between a given range of numbers. For example, say, 10 random numbers between 1 to 6. This function, by default, generates only integer random numbers.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

The syntax for RANDBETWEEN Function

Randbetween syntax

Arguments of RANDBETWEEN Function:

bottom: A required argument defining the smallest value the function could return.

Top: A required argument that defines the largest value that the function could return.

We will move towards an example and see how RANDBETWEEN can be used differently.

How to Generate Random Numbers in Excel?

Let’s understand how to generate the RANDBETWEEN in Excel with some examples.

You can download this RANDBETWEEN Excel Template here – RANDBETWEEN Excel Template

Example #1 – RANDBETWEEN to Generate Random Numbers

Suppose you want to generate random numbers between 1 to 10. You can use RANDBETWEEN to do so.

Step 1: Start typing the RANDBETWEEN formula in cell A2.

RANDBETWEEN in Excel 1-1

Step 2: Enter the bottom number 1 and the top 10.

RANDBETWEEN in Excel 1-2

Step 3: Close the bracket and press Enter key to see the output.

RANDBETWEEN in Excel 1-3

A single random number between 1 to 10 is generated under cell A2.

Step 4: If you want to generate 10 random numbers between 1 to 10, drag the formula across the next 9 rows (until A10). See the screenshot below.

RANDBETWEEN in Excel 1-4

Example #2 – RANDBETWEEN to Generate Negative Numbers

We can also use negative numbers as an argument to generate random numbers. Meaning we can generate a random number between -5 and +5.

Step 1: Start typing the RANDBETWEEN formula in cell A2.

RANDBETWEEN in Excel 2-1

Step 2: Enter the bottom number as -5 and the top as 5.

RANDBETWEEN in Excel 2-2

Step 3: Complete the formula by closing parentheses and Press Enter to see the output.

RANDBETWEEN in Excel 2-3

You can also generate multiple random numbers between -5 to 5. See the screenshot below.

RANDBETWEEN in Excel 2-4

As said earlier, the function is volatile; you may see different results every time the formula gets refreshed, or the sheet is opened.

Example #3 – RANDBETWEEN to Generate Random Numbers with Decimals

RANDBETWEEN usually generates integer random numbers between the ranges. However, with some modifications under formula arguments, generating random numbers with decimals is possible.

Suppose we want to generate random numbers with one decimal after the integer part between 5 to 15. We will see how it can be done step by step.

Step 1: Start typing the RANDBETWEEN formula in cell A2.

Generate Random Numbers with Decimals 3-1

Step 2: While giving the bottom and top arguments, multiply them with 10 and close the parentheses as we want the output as one decimal after the integers. See the screenshot below.

Generate Random Numbers with Decimals 3-2

Step 3: Now, divide by 10 to the entire formula to get decimal output and press the Enter key.

Generate Random Numbers with Decimals 3-3

You can generate multiple random numbers by copying the formula. If you want the data up to two decimal places, multiply the bottom and top values by 100. I want three decimals to multiply by 1000 and so on.

Example #4 – RANDBETWEEN and DATEVALUE to Generate Random Date

We can also generate random dates using a combination of the RANDBETWEEN and DATEVALUE functions in Excel. Follow the steps below:

Suppose we want to generate random dates between August 01, 2019, and August 27, 2019. Follow the steps below to do so.

Step 1: Select all the rows where you want the dates to appear randomly, and in the first cell, start typing the RANDBETWEEN formula in cell A1.

DATEVALUE to Generate Random Date 4-1

Step 2: Use DATEVALUE to input the bottom date as August 01, 2019, and the top date as August 28, 2019. Make sure the dates are in Excel compatible date format.

DATEVALUE to Generate Random Date 4-2

Step 3: Press CTRL + Enter to apply this formula under all selected cells and generate random dates.

DATEVALUE to Generate Random Date 4-3

Excel stores the date in its backend as several days from 01-01-1900. You can see these dates are in several formats. However, we need to convert these dates into a proper date format.

Step 4: Click the Home tab under the number formatting section, change the format to Long Date, and press Enter key.

DATEVALUE to Generate Random Date 4-4

You’ll be able to see the dates generated randomly between August 01, 2019, to August 14, 2019, as shown below.

DATEVALUE to Generate Random Date 4-5

This is from this article. Let’s wrap things up with some points to be remembered.

Things to Remember About RANDBETWEEN in Excel

  • It generates random numbers between the bottom and top numbers.
  • If the bottom (smallest value) exceeds the top (largest value), you will get a #NUM! Error in the RANDBETWEEN formula.
  • It is a volatile function; therefore, every time the sheet gets refreshed/calculated, it will change the values for random numbers under a given range.
  • To stop the formula from being volatile/calculating sheet every time, we need to press F9 when the RANDBETWEEN formula is completed under the formula bar. Or we can paste our results as values to remove the threat of the sheet being calculated.
  • This function can only generate Integer numbers by default. However, some amendments under the formula can also allow you to generate the decimal output.

Recommended Articles

This is a guide to RANDBETWEEN in Excel. Here we discuss How to Generate RANDBETWEEN in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. Random Numbers in Excel
  2. Excel RAND Function
  3. Excel Function for Range
  4. VBA Random Number
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Excel VBA Bundle500+ Hours of HD Videos | 15 Learning Paths | 120+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

Download RANDBETWEEN Excel Template

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download RANDBETWEEN Excel Template

EDUCBA

डाउनलोड RANDBETWEEN Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more