CODEWITHSUNDEEP
excelrandom
John Woo
John Woo

Reputation: 263803

Excel Randomize Decimal Number

I found a very nice function in MS Excel 2007 which is RANDBETWEEN(bottom, top). The problem with this is that it randomize whole number only. Using this formula:

=RANDBETWEEN(7.0, 9.9)

yields

8.0
9.0
7.0
7.0
etc...

How can I modify it so that it will also produce decimal numbers like below

7.5
7.2
9.4
9.5
7.1
8.5
etc...

Upvotes: 7

Views: 44425

Answers (3)

Deepesh
Deepesh

Reputation: 840

Refer this http://ms-office.wonderhowto.com/how-to/generate-random-numbers-with-decimals-excel-338595/

Random decimal number between 30-40

=rand()*10+30

Random decimal number between 0-100

=rand()*100+0

Upvotes: 3

Rich Pollock
Rich Pollock

Reputation: 1190

Since RANDBETWEEN() used to be available exclusively in the Analysis ToolPak (and produced a #NAME? error when the ToolPak was not installed), you might be better off using RAND() for backwards compatibility, multiplying and adding to shift the range and rounding to one decimal place:

=ROUNDUP(7 + RAND() * (9.9 - 7 - 0.1), 1)    ' Excludes 7.0 and 9.9
=ROUNDUP(7 + RAND() * (9.9 - 7), 1)          ' Excludes 7.0, includes 9.9    
=ROUNDDOWN(7 + RAND() * (9.9 - 7), 1)        ' Includes 7.0, excludes 9.9
=ROUNDDOWN(7 + RAND() * (9.9 - 7 + 0.1), 1)  ' Includes 7.0 and 9.9

But the RANDBETWEEN() issue is just a backwards compatibility issue now, so if you're sure your spreadsheet will only be used in Excel 2007 and up, you'll be fine with either approach.

Upvotes: 2

Vicky
Vicky

Reputation: 13244

Use randbetween(70,99) and divide the result by 10.

Upvotes: 32

Related Questions