Generate 1000 random numbers with min, max and average

fastballfreddy

Board Regular
Joined
Jan 13, 2015
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
I want to generate 1,000 random numbers with a min of -10, a max of 50 and an average of 3. I've searched and found a formula but it doesn't allow me to control the min and max. I know there is randbetween but not sure how to use that get the average I want. Any help would be greatly appreciated! Thank you in advance!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Search for a randtriang generator, create a stratified sample, and shuffle the output, I suggest.
 
Upvote 0
Here' a macro :
VBA Code:
Sub RandBetween_Average()
Dim min%: min = -10
Dim max%: max = 50
Dim nbr%: nbr = 1000
Dim avg%: avg = 3
Dim r%, dev%

With [A1].Resize(nbr)
    .Formula = "=RANDBETWEEN(" & min & "," & max & ")"
    .Value = .Value
End With
dev = Application.WorksheetFunction.sum([A1].Resize(nbr)) - avg * nbr

r = 1
Do While dev <> 0
    If Cells(r, 1) = min Or Cells(r, 1) = max Then GoTo nxt
    If dev > min Then
        Cells(r, 1) = Cells(r, 1) - 1
        dev = dev - 1
    Else
        Cells(r, 1) = Cells(r, 1) + 1
        dev = dev + 1
    End If
nxt:
    r = r + 1
    If r > nbr Then r = 1
Loop
End Sub
 
Upvote 0
Amended :
VBA Code:
Sub RandBetween_Average()
Dim min: min = -10
Dim max: max = 50
Dim nbr: nbr = 1000
Dim avg: avg = 3
Dim r%, tot%, dev%

With [A1].Resize(nbr)
    .Formula = "=RANDBETWEEN(" & min & "," & max & ")"
    .Value = .Value
End With
dev = Application.WorksheetFunction.sum([A1].Resize(nbr)) - avg * nbr

r = 1
Do While dev <> 0
    If dev >= min Then
        If Cells(r, 1) = min Then GoTo nxt
        Cells(r, 1) = Cells(r, 1) - 1
        dev = dev - 1
    Else
    If Cells(r, 1) = max Then GoTo nxt
        Cells(r, 1) = Cells(r, 1) + 1
        dev = dev + 1
    End If
nxt:
    r = r + 1
    If r > nbr Then r = 1
Loop
End Sub
 
Upvote 0
Search for a randtriang generator, create a stratified sample, and shuffle the output, I suggest.
Example:
1000_Rands_with_min_max_avg.xlsx
ABCD
1MinAverageMax
2-10350
3Steps between Min and AvgSteps between Avg and Max
4783,3333333216,6666667
5Random Numbers"Stratified" Sample (#1000 adjusts to Avg)Shuffle #Output
61-100,330282811
72-100,14661982619
83-100,3016687282
94-100,00441288949
105-100,05270752638
Tabelle1
Cell Formulas
RangeFormula
A4A4=(C2-B2)/(C2-A2)*1000
C4C4=(B2-A2)/(C2-A2)*1000
B6:B10B6=IF(A6<=$A$4,$A$2+ROUND(($A6-1)*($B$2-$A$2)/$A$4,0),$B$2+ROUND(($A6-$A$4)*($C$2-$B$2)/$C$4,0))
C6:C10C6=RAND()
D6:D10D6=INDEX(B6:B1005,RANK(C6:C1005,C6:C1005))
A7:A10A7=A6+1
Press CTRL+SHIFT+ENTER to enter array formulas.


Please note:
1. A7:B7 need to be copied down to row 1005; formulas in C and D need to be array-entered into rows 6:1005.
2. Cell B1005 you need to set to 33 to achieve the average of 3 (due to rounding differences in between)
3. Of course these numbers are not really random, but I hope they are random enough
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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