Random Numbers with Decimals

markusreyes2907

New Member
Joined
Jul 14, 2020
Messages
34
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
 
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) :)
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
'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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top