Random Numbers with Decimals

markusreyes2907

New Member
Joined
Jul 14, 2020
Messages
30
Office Version
  1. 2013
Platform
  1. Windows
I've looked online and found a way to create random numbers with decimals. However, the formula I found online isn't working exactly how I thought it would. I want random numbers to generate between the numbers 20-22 with only one decimal place. I get some numbers that are rounded, some that are not, along with values that are not between 20-22. I've also used single parenthesis with Round and it seems to do the exact same. How can I fix this problem? Below is my code you can use to see what's happening.

VBA Code:
Dim rng As Range, num As Byte, x, i As Double, j

num = InputBox("Enter number here", "Input", 5)

Set rng = Range("A3")
rng.Resize(, 5) = Array("Treatment", "Mean", "Std Dev", "Min", "Max")
rng.Resize(, 5).Borders(4).Weight = xlThin
rng.Offset(1).Resize(num) = Evaluate("row(" & Cells(1).Resize(num).Address & ")")

For j = 1 To 4
    For x = 1 To num
    i = Round((20 + Rnd * 22), 1)
    rng.Offset(x, j) = i
    Next
Next
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
You said "uncomment", but it is already uncommented. If you meant "comment out" or if a reader reads quickly and interprets that you meant this... you should not do that. If I am not mistaken, without the Randomize statement, the Rnd function will generate the same series of number each time the workbook is opened and your function is used.
'Fraid I didn't (mean "comment out") and you are (mistaken) :)
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,410
Office Version
  1. 2010
Platform
  1. Windows
'Fraid I didn't (mean "comment out") and you are (mistaken) :)
Then I don't understand what you meant for this part of your code...
VBA Code:
Randomize 'Uncomment if you like
as the Randomize statement is already uncommented, so what are you saying should be "uncommented" then?

And if your "...you are (mistaken)" comment is referring to my statement that " without the Randomize statement, the Rnd function will generate the same series of number each time the workbook is opened and your function is used"... I am not. Here is a simple test you can perform to see why the Randomize statement is absolutely needed. Open a new workbook and put this code in a general Module...
VBA Code:
Sub TestRnd()
  Dim X As Long
  For X = 1 To 10
    Debug.Print Rnd
  Next
End Sub
Run the code and copy the results into, say, a Notepad window so you can compare them in a moment. Now save the workbook and then close it. Now open the workbook and run the macro and compare the output to what you saved from the last time you ran it. You will notice the list is the same. Without the Randomize statement, the list will always be the same every time you run the code after opening the workbook.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,747
Messages
5,543,975
Members
410,587
Latest member
kaloybulan
Top