# Random Numbers with Decimals

#### markusreyes2907

##### New Member
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``````

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Fluff

##### MrExcel MVP, Moderator
VBA Code:
``    i = Application.RoundDown((22 - 20 + 1) * Rnd + 20, 1)``

#### markusreyes2907

##### New Member
VBA Code:
``    i = Application.RoundDown((22 - 20 + 1) * Rnd + 20, 1)``
Perfect that worked! Why use RoundDown specifically?

#### Fluff

##### MrExcel MVP, Moderator
If you use round & have a value of 22.99 it will round to 23

#### markusreyes2907

##### New Member

If you use round & have a value of 22.99 it will round to 23
Oh, yes of course. Thanks for all the help!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

#### Sulprobil

##### Board Regular

VBA Code:
``````Function sbRndDecPlace(dmin As Double, dmax As Double, _
Optional ldecplaces As Long = 0) As Double
'Creates a random number between dmin and dmax with
'ldig decimal places. All possible (dmax - dmin) *
'10 ^ ldig + 1 values appear with same likelihood.
'Reverse("moc.LiborPlus.www") PB V0.2 16-Oct-2009
Randomize 'Uncomment if you like
sbRndDecPlace = Int((dmax - dmin + 10 ^ -ldecplaces) * _
10 ^ ldecplaces * Rnd) * 10 ^ -ldecplaces + dmin
End Function``````

#### Rick Rothstein

##### MrExcel MVP
VBA Code:
``    i = Application.RoundDown((22 - 20 + 1) * Rnd + 20, 1)``
I would think this would also work...
VBA Code:
``i = Application.RandBetween(200, 220) / 10``

#### Fluff

##### MrExcel MVP, Moderator
As always there's more than one way to do anything.
I was just correcting the OP's layout.

#### Rick Rothstein

##### MrExcel MVP
Randomize 'Uncomment if you like
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.

Last edited:

Replies
9
Views
122
Replies
1
Views
42
Replies
1
Views
154
Replies
1
Views
79
Replies
13
Views
129