Generate a set of random numbers with the specific AVERAGE

BonScie

BonScie

New Member
Joined
Jul 13, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hi there, upon searching around the net I came across with Mr.Eric W.'s reply with one of the post here regarding generating random numbers with target overall total.

Link: generating a set of random numbers to total a set value

I have pretty much same problem but the target is AVERAGE of the generated random number instead of the overall total.

Your help will be much appreciated. Thank you in advance.
 

Attachments

  • GivenFigures.png
    GivenFigures.png
    5.4 KB · Views: 151

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Welcome to the MrExcel forum!

I don't have the time right now to customize those formulas for your particular situation, but you should be able to use them as is, by changing your input parameters. First, if you want the target average to be 11.55 for 298 numbers, that's mathematically equivalent to saying you want the target sum to be 11.55 X 298 = 3441.90. So set your target sum to 3441.90. Next, the original formulas were set up to work on integers. The RANDBETWEEN function works on integers. So if you want 2 decimal places, multiply everything by 100, the target value (344190), and Min/Max values (845, 1555). Then when you get the list, divide everything by 100.

If I get some more time, I'll take another look at this.
 
Upvote 0
Hello,
I was interested about this problem and I was trying to create some VBA code to resolve him.
Here it is...
It runs pretty fast, even with numbers greater then 10000.
Hope it will be useful.
VBA Code: 
Sub SpecificAvargeNumbersGenerator()
   
    Dim vA(), vSum As Double
    Dim vMin  As Double, vAverage As Double, _
        vMax As Double, vNumbers As Double
   
    vMin = 8.45
    vAverage = 11.55
    vMax = 15.55
    vNumbers = 298
   
    If vAverage <= vMin Or vAverage >= vMax Then _
        MsgBox "Average need to be inside vMin and vMax.": _
        Exit Sub
    ReDim Preserve vA(1 To vNumbers, 1 To 1)
    With Application
        .ScreenUpdating = False
        Columns(1).ClearContents
        Randomize
        vMid = .Min(vAverage - vMin, vMax - vAverage)
        vLeft = vAverage - vMid
        vRight = vAverage + vMid
        For vN = 1 To vNumbers
E1:         vN1 = Rnd() * vRight
            If vN1 < vLeft Then GoTo E1
            vA(vN, 1) = vN1
            vSum = vSum + vN1
        Next vN
        vDif = (vNumbers * vAverage - vSum) / vNumbers
        For vN = 1 To vNumbers
            vA(vN, 1) = vA(vN, 1) + vDif
        Next vN
        For vN = 1 To vNumbers
            If vA(vN, 1) < vMin Then
                vP = .Match(.Max(vA), vA, False)
                vA(vP, 1) = vA(vP, 1) - vDif - 2
                vA(vN, 1) = vA(vN, 1) + vDif + 2
             ElseIf vA(vN, 1) > vMax Then
                vP = .Match(.Min(vA), vA, False)
                vA(vP, 1) = vA(vP, 1) + vDif + 2
                vA(vN, 1) = vA(vN, 1) - vDif - 2
            End If
        Next vN
    End With
    [A1].Resize(vNumbers) = vA
   
End Sub
 
Last edited:
Upvote 0
Eric W said:
Welcome to the MrExcel forum!

I don't have the time right now to customize those formulas for your particular situation, but you should be able to use them as is, by changing your input parameters. First, if you want the target average to be 11.55 for 298 numbers, that's mathematically equivalent to saying you want the target sum to be 11.55 X 298 = 3441.90. So set your target sum to 3441.90. Next, the original formulas were set up to work on integers. The RANDBETWEEN function works on integers. So if you want 2 decimal places, multiply everything by 100, the target value (344190), and Min/Max values (845, 1555). Then when you get the list, divide everything by 100.
Click to expand...

Good day, Mr.Erick! I got the logic and I'm excited to try it. I'll keep you updated. Thank you so much for giving me a bit of your precious time. Keep safe and God bless!
 
Upvote 0
EXCEL MAX said:
Hello,
I was interested about this problem and I was trying to create some VBA code to resolve him.
Here it is...
It runs pretty fast, even with numbers greater then 10000.
Hope it will be useful.
VBA Code: 
Sub SpecificAvargeNumbersGenerator()
  
    Dim vA(), vSum As Double
    Dim vMin  As Double, vAverage As Double, _
        vMax As Double, vNumbers As Double
  
    vMin = 8.45
    vAverage = 11.55
    vMax = 15.55
    vNumbers = 298
  
    If vAverage <= vMin Or vAverage >= vMax Then _
        MsgBox "Average need to be inside vMin and vMax.": _
        Exit Sub
    ReDim Preserve vA(1 To vNumbers, 1 To 1)
    With Application
        .ScreenUpdating = False
        Columns(1).ClearContents
        Randomize
        vMid = .Min(vAverage - vMin, vMax - vAverage)
        vLeft = vAverage - vMid
        vRight = vAverage + vMid
        For vN = 1 To vNumbers
E1:         vN1 = Rnd() * vRight
            If vN1 < vLeft Then GoTo E1
            vA(vN, 1) = vN1
            vSum = vSum + vN1
        Next vN
        vDif = (vNumbers * vAverage - vSum) / vNumbers
        For vN = 1 To vNumbers
            vA(vN, 1) = vA(vN, 1) + vDif
        Next vN
        For vN = 1 To vNumbers
            If vA(vN, 1) < vMin Then
                vP = .Match(.Max(vA), vA, False)
                vA(vP, 1) = vA(vP, 1) - vDif - 2
                vA(vN, 1) = vA(vN, 1) + vDif + 2
             ElseIf vA(vN, 1) > vMax Then
                vP = .Match(.Min(vA), vA, False)
                vA(vP, 1) = vA(vP, 1) + vDif + 2
                vA(vN, 1) = vA(vN, 1) - vDif - 2
            End If
        Next vN
    End With
    [A1].Resize(vNumbers) = vA
  
End Sub
Click to expand...

Thank you so much! i will definitely try this too. Keep safe and God bless!
 
Upvote 0
Good day, Sir/Ma'am
EXCEL MAX said:
Hello,
I was interested about this problem and I was trying to create some VBA code to resolve him.
Here it is...
It runs pretty fast, even with numbers greater then 10000.
Hope it will be useful.
VBA Code: 
Sub SpecificAvargeNumbersGenerator()
  
    Dim vA(), vSum As Double
    Dim vMin  As Double, vAverage As Double, _
        vMax As Double, vNumbers As Double
  
    vMin = 8.45
    vAverage = 11.55
    vMax = 15.55
    vNumbers = 298
  
    If vAverage <= vMin Or vAverage >= vMax Then _
        MsgBox "Average need to be inside vMin and vMax.": _
        Exit Sub
    ReDim Preserve vA(1 To vNumbers, 1 To 1)
    With Application
        .ScreenUpdating = False
        Columns(1).ClearContents
        Randomize
        vMid = .Min(vAverage - vMin, vMax - vAverage)
        vLeft = vAverage - vMid
        vRight = vAverage + vMid
        For vN = 1 To vNumbers
E1:         vN1 = Rnd() * vRight
            If vN1 < vLeft Then GoTo E1
            vA(vN, 1) = vN1
            vSum = vSum + vN1
        Next vN
        vDif = (vNumbers * vAverage - vSum) / vNumbers
        For vN = 1 To vNumbers
            vA(vN, 1) = vA(vN, 1) + vDif
        Next vN
        For vN = 1 To vNumbers
            If vA(vN, 1) < vMin Then
                vP = .Match(.Max(vA), vA, False)
                vA(vP, 1) = vA(vP, 1) - vDif - 2
                vA(vN, 1) = vA(vN, 1) + vDif + 2
             ElseIf vA(vN, 1) > vMax Then
                vP = .Match(.Min(vA), vA, False)
                vA(vP, 1) = vA(vP, 1) + vDif + 2
                vA(vN, 1) = vA(vN, 1) - vDif - 2
            End If
        Next vN
    End With
    [A1].Resize(vNumbers) = vA
  
End Sub
Click to expand...

Hi! Good day! Kindly help me please, I pasted the VB code in a new excel file and tried running it and encountered this error (image attached). Please bare with me I have limited knowledge in VB. ?
Thank you so much your effort and time are much appreciated.
 

Attachments

  • VB-Error.png
    VB-Error.png
    33.7 KB · Views: 31
Upvote 0
EXCEL MAX said:
Hello,
I was interested about this problem and I was trying to create some VBA code to resolve him.
Here it is...
It runs pretty fast, even with numbers greater then 10000.
Hope it will be useful.
VBA Code: 
Sub SpecificAvargeNumbersGenerator()
  
    Dim vA(), vSum As Double
    Dim vMin  As Double, vAverage As Double, _
        vMax As Double, vNumbers As Double
  
    vMin = 8.45
    vAverage = 11.55
    vMax = 15.55
    vNumbers = 298
  
    If vAverage <= vMin Or vAverage >= vMax Then _
        MsgBox "Average need to be inside vMin and vMax.": _
        Exit Sub
    ReDim Preserve vA(1 To vNumbers, 1 To 1)
    With Application
        .ScreenUpdating = False
        Columns(1).ClearContents
        Randomize
        vMid = .Min(vAverage - vMin, vMax - vAverage)
        vLeft = vAverage - vMid
        vRight = vAverage + vMid
        For vN = 1 To vNumbers
E1:         vN1 = Rnd() * vRight
            If vN1 < vLeft Then GoTo E1
            vA(vN, 1) = vN1
            vSum = vSum + vN1
        Next vN
        vDif = (vNumbers * vAverage - vSum) / vNumbers
        For vN = 1 To vNumbers
            vA(vN, 1) = vA(vN, 1) + vDif
        Next vN
        For vN = 1 To vNumbers
            If vA(vN, 1) < vMin Then
                vP = .Match(.Max(vA), vA, False)
                vA(vP, 1) = vA(vP, 1) - vDif - 2
                vA(vN, 1) = vA(vN, 1) + vDif + 2
             ElseIf vA(vN, 1) > vMax Then
                vP = .Match(.Min(vA), vA, False)
                vA(vP, 1) = vA(vP, 1) + vDif + 2
                vA(vN, 1) = vA(vN, 1) - vDif - 2
            End If
        Next vN
    End With
    [A1].Resize(vNumbers) = vA
  
End Sub
Click to expand...

Hi again! please disregard my last reply. It's just the END SUB command, I unintentionally exclude the command from copying and pasting. OMG... I'm so sorry!
It works great thank you very much for your time. Keep safe and God bless! ?
 
Upvote 0
Eric W said:
Welcome to the MrExcel forum!

I don't have the time right now to customize those formulas for your particular situation, but you should be able to use them as is, by changing your input parameters. First, if you want the target average to be 11.55 for 298 numbers, that's mathematically equivalent to saying you want the target sum to be 11.55 X 298 = 3441.90. So set your target sum to 3441.90. Next, the original formulas were set up to work on integers. The RANDBETWEEN function works on integers. So if you want 2 decimal places, multiply everything by 100, the target value (344190), and Min/Max values (845, 1555). Then when you get the list, divide everything by 100.

If I get some more time, I'll take another look at this.
Click to expand...

Good day again Sir, I'm just sharing the output I made with the equation/logic that you shared. It works fine but when I generate a much bigger number of random numbers the bottom part output given was more of similar numbers (sample file attached). I'm just wondering, is there something I did wrong?

I know that you are a busy and I am very grateful that you reply with my query.

Thank you very much! Stay safe and God bless.

AveNumGen.xlsm
ABCDEFGH
111550011.55Number of random numbersTargetMaxMin
21131013.1010011.5515.558.45
32146314.631001155001555845
43131413.14
54145514.55
65126812.68
76115411.54
87151115.11
98131013.10
109121412.14
11109079.07
1211124012.40
1312100010.00
1413101310.13
1514104910.49
1615138713.87
17169769.76
1817149814.98
1918102910.29
20198888.88
21208528.52
2221128412.84
2322140214.02
2423106510.65
2524108510.85
26258788.78
2726153615.36
2827144514.45
2928106110.61
3029104810.48
31309989.98
3231145714.57
3332120712.07
3433108310.83
3534152015.20
3635113911.39
3736143914.39
3837132413.24
3938147314.73
4039114511.45
4140106910.69
4241115511.55
43429519.51
4443112311.23
4544119311.93
4645120212.02
4746118611.86
4847153715.37
4948118711.87
5049133013.30
5150143714.37
5251151015.10
5352118111.81
5453133713.37
5554132813.28
5655145214.52
5756145014.50
5857142514.25
5958140814.08
60599399.39
61608828.82
6261146714.67
6362135713.57
6463124512.45
6564146614.66
6665135213.52
6766130813.08
6867129312.93
6968152315.23
70699199.19
7170131413.14
7271146214.62
7372142714.27
7473106110.61
75749199.19
7675116011.60
77768868.86
7877135913.59
7978110511.05
8079107410.74
8180114611.46
82818848.84
83828488.48
84838508.50
85848468.46
86858458.45
87868458.45
88878458.45
89888458.45
90898458.45
91908458.45
92918458.45
93928458.45
94938458.45
95948458.45
96958458.45
97968458.45
98978458.45
99988458.45
100998458.45
1011008458.45
Sheet1
Cell Formulas
RangeFormula
B1B1=SUM(B2:B402)
C1C1=AVERAGE(C2:C326)
B2B2=RANDBETWEEN(MAX($H$3,$F$3-(($E$3-ROWS($B$2:$B2))*$G$3)),MIN($G$3,$F$3-(($E$3-ROWS($B$2:$B2))*$H$3)))
C2:C101C2=IF(B2=0,"",B2/100)
B3:B101B3=IF(ROW()=$E$3+3,$F$3-SUM($B$2:$B2),IF(ROW()>$E$3+3,"",RANDBETWEEN(MAX($H$3,$F$3-(SUM($B$2:$B2)+($E$3-ROWS($B$2:$B3))*$G$3)),MIN($G$3,$F$3-(SUM($B$2:$B2)+($E$3-ROWS($B$2:$B3))*$H$3)))))
E3E3=E2
F3F3=(E2*F2)*100
G3:H3G3=G2*100
 
Upvote 0
No, you did nothing wrong, it's just a weakness of the algorithm I used. The way it works is it selects the first number in the accepted range. The next number it selects is more constrained since the total is fixed. I handle this by adjusting the acceptable upper and lower bounds for that number. After enough numbers, those acceptable bounds get closer and closer, until they converge on either the high or low bound. I don't know a good way to handle that with formulas only. I can think of a few ways using VBA, but it seems that EXCEL MAX has provided a workable macro already. Let me know if you want another option.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K0st4din
  • Solved
Macros Serial number generation with repeatability comparison base
2 3
Replies
20
Views
822
StephenCrump
S
S
  • Question Question
analyze and export based on multiple criteria
Replies
0
Views
526
slash32487
S
A
  • Solved
Random 0/1 array with set numbers of 1s
2
Replies
11
Views
1K
Augustinn
A
A
  • Question Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
884
ashleighbrown
A
N
  • Question Question
Random number in every other cell paired then sort.
Replies
6
Views
666
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

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