Generate a Random Value between Two Limits in Microsoft Excel

 

In this article, we will learn how to generate random number between two values in Microsoft Excel.

To generate a list of the random values between two limits, we will use of RANDBETWEEN function.

Example of RANDBETWEEN function used in our life is ATM PIN, Net Banking password.

RANDBETWEEN returns a random integer number between the two specified (bottom & top) numbers. A new random integer number gets generated every time the worksheet is calculated.

 

Syntax: =RANDBETWEEN(bottom,top)

 

bottom: It is the smallest integer value that RANDBETWEEN will return.

top: It is the largest integer value that RANDBETWEEN will return.

 

Note: If bottom value is greater than top value then RANDBETWEEN function will return #NUM! error.

 

Let us take an example:

 

  • You want to generate a list of random numbers in column A
  • The two arguments i.e. bottom & top have assigned with the values as 5 and 50
  • Select the cell in which you want to generate random number list.
  • Enter the formula in range =RANDBETWEEN(5,50)

img1

  • Copy the formula down in below range of cells

img2

  • You will notice in cell A2, the number gets automatically changed from 40 to 16.
  • This is because of the automatic calculation feature in Excel. If you do not want Excel to generate a new list or disable to re-calculate feature.
  • You need to click on Formulas ribbon
  • In Calculation group, click on Calculation Options

img3

  • Select Manual

If you enter bottom value greater than top value then RANDBETWEEN function will return #NUM! error.

Formula in cell G3 =RANDBETWEEN(50,5) will return #NUM! error.

img4

In this way we can generate Random numbers using Excel Randbetween function.



2 thoughts on “Generate a Random Value between Two Limits in Microsoft Excel

  1. “this formula does not work in my Excel 95.
    actually, what I’m looking for is a “”formula”” that will generate, for example, 24 non repeating numbers between 1 and 24.”

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>