Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Generating Double-Digit Random Numbers.

Generating Double-Digit Random Numbers

by Allen Wyatt
(last updated October 25, 2014)

8

Venkataramanan needs to generate random numbers in the range of -99 to +99, excluding single-digit numbers (-9 to +9). He wonders if there is a way to accomplish the task.

There are a couple of worksheet functions that are often used to generate random numbers in Excel. The RAND function is used to generate a random number between 0 and 1, while the RANDBETWEEN function is used to generate a random number within a range of numbers.

There is no function to do what Venkataramanan wants to do, but you can write a formula that will do the trick. Consider this formula:

=IF(RAND()>0.5,1,-1)*(RANDBETWEEN(10,99))

The first RAND function determines if the result is '+' or '-' and the next RANDBETWEEN function returns the desired number between 10 and 99. When the function is done, you have the desired double-digit random number.

Another formula is similar in nature:

=ROUND(RAND()*89+10,0)*((RAND()<0.5)*2-1)

The first part generates whole numbers in the range of 0 through 89. The formula adds 10 to this, effectively giving a number from 10 to 99. The second part of the formula is then used to randomly determine whether the result should be positive or negative.

Another approach relies entirely on the RANDBETWEEN function and doesn't use any multiplication:

=VALUE(IF(RANDBETWEEN(0,1)=0,"-","")&RANDBETWEEN(1,9)&RANDBETWEEN(0,9))

The formula puts together a string that consists of either a minus sign or a blank followed by two digits. The formula then uses the VALUE function to convert the string to a numeric value. An even shorter version of the formula would be this:

=VALUE(IF(RANDBETWEEN(0,1)=0,"-","")&RANDBETWEEN(10,99))

Another similar formula is the following:

=RANDBETWEEN(10,99)*IF(RANDBETWEEN(0,1),1,-1)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10611) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Generating Double-Digit Random Numbers.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Making Text Bold

Want a cool shortcut to make your text bold? Here's a method that fits in wonderfully with how things are done in the ...

Discover More

Develop Macros in Their Own Workbook

If you develop macros and edit them quite a bit, you may be running the risk of causing problems with the macros or with ...

Discover More

Ignoring Punctuation in Names

If you have a word that includes punctuation as part of the word itself, then you may be frustrated by how Word treats ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Simulating Alt+Enter in a Formula

You can use the Alt+Enter keyboard shortcut while entering information in order to force your data onto multiple lines in ...

Discover More

Calculating the Distance between Points

Want to figure out how far it is between two points on the globe? If you know the points by latitude and longitude, you ...

Discover More

Quickly Adding Formulas Referencing Multiple Worksheets

When you need to pull information from a lot of different worksheets into a single worksheet, it can be baffling to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is eight minus 8?

2014-10-29 17:27:38

Aldo

I like =IF(RANDBETWEEN(1,2)=1,RANDBETWEEN(10,99),-RANDBETWEEN(10,99))

:-)


2014-10-28 17:17:26

Bigger Don

@Ken & @Rick

My guess is you two have been math majors of some type at some point in your lives...and most of what you know on this topic is well over the head of this simple MBA guy. For most of us, we look at the random numbers we're getting from Excel like inventory counters look at a pile of beans; they're "random enough". Not being flippant, just saying there are different perspectives.

Now, I'm smart enough to understand that if you multiple two random numbers the result is flawed because primes will be excluded except when one of them equals 1 (or negative 1) and the other is a prime.

That said, consider a binary random decision resulting in 1 or -1, and that result multiplying a ranged random number (RANDBETWEEN):

= IF(RANDBETWEEN(1,2)=1,-1,1)*RANDBETWEEN(10,99)

Why would that not produce a "uniformly random result"?


2014-10-27 17:12:26

Ken Kast

The product of two random variables with uniform distribution does not have uniform distribution. So while producing unpredictable values, none of these formulas that rely on two calls to RAND or RANDBETWEEN will produce a uniformly random result.


2014-10-27 10:51:12

Rick

Allen's second formula:
=ROUND(RAND()*89+10,0)*((RAND()<0.5)*2-1)

does not produce a uniform distribution.
RAND()*89+10 produces a real number between 10 and 99 (call it x). ROUND(x,0) will produced the value 10 only if x is between 10 and 10.5. Similarly, ROUND(x,0) will produced the value 99 only if x is between 98.5 and 99. Thus 10 and 99 will be produced half as often as each of the other integers 11-98.

=TRUNC(RAND()*90+10,0)*((RAND()<0.5)*2-1)

should work


2014-10-27 05:24:13

Michael (Micky) Avidan

@BHershman,
"Excel, is capable to perform all kind of tasks - except three [two of which are illegal outside the State of Florida]".
It is very(!) easy to produce a range of Random numbers which will be UNIQUE (Never(!) repeat themselves) but this is, of course, another episode.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2014-10-26 10:02:25

BHershman

Michael (Micky) Avidan:

I may have misread (again!) the details of the hint - which makes my comment irrelevant but NOT false. The essence of a random-number generator is that the chance of generating the SAME number twice in quick succession is very small.

I may be in need of a brain change but not a computer change.


2014-10-26 06:18:57

Michael (Micky) Avidan

@BHershman,
If the above proposed Formulas produces - every press on [F9] key (Reaalculation) - the same Random value then, to my opinion - with regret and sympathy - it is a bout time, for you, to replace your computer...Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2014-10-25 14:59:52

BHershman

The essence of computer-generated RANDOM numbers is that each time the related function is called a DIFFERENT result is generated! Therefore any formula that makes 2 or more calls to a random function (as do all the formulas in the tip) cannot be expected to work as though each call produces the same test value!

The only reliable way of doing what is wanted is to write a user-defined function such as:
FUNCTION RAND2DIG() AS LONG
DIM RANDSTORE AS LONG
LOOP
RAND2DIG = WORKSHEETFUNCTION.RANDBETWEEN(-99,99)
UNTIL ABS(RAND2DIG) >= 10
END FUNCTION


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.