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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
    i = Application.RoundDown((22 - 20 + 1) * Rnd + 20, 1)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
If you use round & have a value of 22.99 it will round to 23
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
114
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 18, 2011
Messages
36,410
Office Version
  1. 2010
Platform
  1. Windows
How about
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
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
As always there's more than one way to do anything.
I was just correcting the OP's layout.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,410
Office Version
  1. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,113,749
Messages
5,543,978
Members
410,587
Latest member
kaloybulan
Top