Random Numbers with Decimals

[markusreyes2907]

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.

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

 

[Fluff]

How about

    i = Application.RoundDown((22 - 20 + 1) * Rnd + 20, 1)

 

[markusreyes2907]

How about

    i = Application.RoundDown((22 - 20 + 1) * Rnd + 20, 1)

Perfect that worked! Why use RoundDown specifically?

 

[Fluff]

If you use round & have a value of 22.99 it will round to 23

 

[markusreyes2907]

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]

You're welcome & thanks for the feedback.

 

[Sulprobil]

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]

How about

    i = Application.RoundDown((22 - 20 + 1) * Rnd + 20, 1)

I would think this would also work...

i = Application.RandBetween(200, 220) / 10

 

[Fluff]

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

 

[Rick Rothstein]

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.