radom number generation HELP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i want to generate a random number. with high and low
limits. but i want this number to have two decimal places
and to be copied into different cells, genrationg a new
number every time.I feel like i've tried everything so
help would be appreaciated
 
I think Rand and randbetween are such that you have one, you have both, so
there is randbetween(low number, high number).

Or, directly from Excel help, there is:

To generate a random real number between a and b, use:
RAND()*(b-a)+a

Since you probably want values larger than 1, I would use randbetween to
avoid having to multiply the rand() formula to get the range you're looking
for--

Since you want 2 decimal places, try:
=ROUND(RANDBETWEEN(100,200)+RAND(),2)

for numbers with 2 random decimal places between 100 and 200.

...you can copy into various cells, and the numbers will reevaluate when any
change is made on the worksheet (for example, pressing DEL while in any cell
[a blank one]).
 
2dps in actuality or just formatted as 2 dps. Here is one I use where I simply
select a range, hit a key combo and it does the rest (Prompts you for a high and
low limit and integers or decimals). Data has more than 2dps in the first one
but is formatted as 2 dps. Second one actually has only 2 dps. Pretty sure
most of it came from Tom Ogilvy but it was a while ago now.

Sub RandomNumber()
ubnd = InputBox("Enter Upper Bound")
lbnd = InputBox("Enter Lower Bound")
nudp = InputBox("Just hit OK for Integers or type D for decimals")
If UCase(nudp) = "D" Then
With Selection
.ClearContents
.NumberFormat = "#,##0.00"
End With
For Each cell In Selection
cell.Value = Rnd() * (ubnd - lbnd) + lbnd
Next cell
Else
With Selection
.ClearContents
.NumberFormat = "#,##0"
End With
For Each cell In Selection
cell.Value = Int(Rnd() * (ubnd - lbnd + 1) + lbnd)
Next cell
End If
End Sub


If you actually only want 2 dps then use this one, but can't guarantee no
repeats

Sub RandomNumber()
ubnd = InputBox("Enter Upper Bound")
lbnd = InputBox("Enter Lower Bound")
nudp = InputBox("Just hit OK for Integers or type D for decimals")
If UCase(nudp) = "D" Then
With Selection
.ClearContents
.NumberFormat = "#,##0.00"
End With
For Each cell In Selection
cell.Value = Int((Rnd() * (ubnd - lbnd) + lbnd) * 100) / 100
Next cell
Else
With Selection
.ClearContents
.NumberFormat = "#,##0"
End With
For Each cell In Selection
cell.Value = Int(Rnd() * (ubnd - lbnd + 1) + lbnd)
Next cell
End If
End Sub
 
Back
Top