Random numbers between two values with condition

[techfreak]

Hi all

I'm stuck with a mental block here:

I want to generate 20 individual random numbers between two values (-3.28 and 14.50) but with a condition that the average of the 20 numbers should equal a set value, in this case 5.61

Can someone help please?

 

[Paul Ked]

If you need the numbers to meet a criteria then they can't be random... Random means Random!

You could generate random numbers and then tweek them to make the average you require, but then you lose the random aspect of the numbers.

 

[techfreak]

Hi Paul

Thanks for the reply. I don't think I explained myself clearly as I think one of us is misunderstood. Perhaps it's me and there's another function besides 'randbetween' that can achieve what I want?

For example, here's list of 20 randomly generated numbers chosen between -3 and 14 of which the average is 5.6 (rounded to keep this example simple).

11,10,11,10,5,-1,-3,13,-3,11,-2,9,7,7,7,3,6,-3,3,12,6

so, of course it can be done, I can manually generate a vast number of 20 individual random combinations between two values that average a given value, so I'm sure this can be done by excel but as I say, perhaps it requires another function?

 

[Paul Ked]

You could do this:

Sub test()
    Dim i As Long
     Randomize
     Do
        For i = 1 To 20
            Cells(i, 1) = -3.28 + 14.5 * Rnd()
        Next
    Loop Until Cells(1, 3) > 5.59 And Cells(1, 3) < 5.62
End Sub

which will do what you want, BUT it could also lock your system up for a while!

 

[DanteAmor]

I want to generate 20 individual random numbers between two values (-3.28 and 14.50) but with a condition that the average of the 20 numbers should equal a set value, in this case 5.61

You can set the minimum and maximum values ​​and the average in the cells as shown in the following image.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Min</td><td >Max</td><td >Average</td><td >Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">-3.28</td><td style="text-align:right; ">14.5</td><td style="text-align:right; ">5.61</td><td style="text-align:right; ">-2.42</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td style="text-align:right; ">13.64</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-0.48</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td style="text-align:right; ">11.70</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td style="text-align:right; ">4.94</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td style="text-align:right; ">6.28</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-2.45</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td style="text-align:right; ">13.67</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td style="text-align:right; ">10.33</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.89</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td style="text-align:right; ">6.47</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td style="text-align:right; ">4.75</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td style="text-align:right; ">-2.95</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td style="text-align:right; ">14.17</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.39</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td > </td><td style="text-align:right; ">10.83</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td style="text-align:right; ">9.81</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td > </td><td > </td><td style="text-align:right; ">1.41</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td > </td><td style="text-align:right; ">2.09</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td > </td><td > </td><td style="text-align:right; ">9.13</td></tr></table>

Try this macro:

Sub randb()
  Dim i As Long
  For i = 1 To 20 Step 2
    Cells(i + 1, "D") = Round([A2] + [B2] * Rnd(), 2)
    Cells(i + 2, "D") = ((i + 1) * [C2]) - WorksheetFunction.Sum(Range("D2:D" & i + 1))
  Next
End Sub

 

[Paul Ked]

Or using an array is quick!

Sub test()
    Dim i As Long, arr(0 To 19)
    Randomize
    Do
        For i = 0 To 19
            arr(i) = -3.28 + 14.5 * Rnd()
        Next
    Loop Until Application.Average(arr) > 5.605 And Application.Average(arr) < 5.615
    For i = 0 To 19
        Cells(i + 1, 1) = arr(i)
    Next
End Sub

 

[Paul Ked]

Dante, that's clever

 

[techfreak]

YESSS Dante! That's worked perfectly. I knew there would be a simple macro for this, I must get round to learning how to do this.

Paul, was worried about running your original macro and being locked in a timeloop for years ahead until it landed on the exact average

Thanks again guys

 

[Paul Ked]

Ha! The original came out like this in tests:

Average of 5.59987054288387 in 10.12109375 secs with 326 tries

Average of 5.61696160256863 in 4.1015625 secs with 185 tries

Average of 5.60427415788174 in 13.38671875 secs with 590 tries

Average of 5.59763451039791 in 9.53125 secs with 411 tries

Average of 5.60301578462124 in 5.890625 secs with 257 tries

Average of 5.61176562726498 in 5.375 secs with 233 tries

Average of 5.60131421506405 in 5.90625 secs with 268 tries

Average of 5.6197777312994 in 1.49609375 secs with 48 tries

Average of 5.61121941030025 in 1.97265625 secs with 90 tries

Average of 5.59769466340542 in 0.015625 secs with 17 tries

Average of 5.60658693730831 in 5.05859375 secs with 219 tries

Average of 5.61038971364498 in 9.05859375 secs with 388 tries

Changed Average setting to +/- 0.05

Average of 5.60971558511257 in 6.29296875 secs with 279 tries

Average of 5.61160107076168 in 3.8515625 secs with 145 tries

Average of 5.61145794808865 in 4.78125 secs with 215 tries

Average of 5.60723963201046 in 28.90625 secs with 1293 tries

Average of 5.61169302880764 in 24.81640625 secs with 1055 tries

Average of 5.6061140102148 in 11.640625 secs with 509 tries

Average of 5.61188939034939 in 63.609375 secs with 2471 tries

<tbody>
</tbody>

Using the array:

Average of 5.61055980145931 in 0.52734375 secs with 191 tries

Average of 5.60867915570736 in 1.37890625 secs with 736 tries

Average of 5.60868814408779 in 0.30859375 secs with 91 tries

Average of 5.61208851754665 in 0.1328125 secs with 362 tries

Average of 5.6137078088522 in 2.61328125 secs with 1786 tries

Average of 5.61319685399532 in 0.046875 secs with 1019 tries

Average of 5.60553322255611 in 0.44921875 secs with 204 tries

Average of 5.61214867055416 in 5.88671875 secs with 5088 tries

Average of 5.61311319291592 in 2.94921875 secs with 2229 tries

Average of 5.61340427815914 in 2.8125 secs with 1971 tries

Average of 5.61186104238033 in 2.9921875 secs with 2232 tries

Average of 5.61213415086269 in 0.14453125 secs with 285 tries

Average of 5.61149874150753 in 0.36328125 secs with 367 tries

Average of 5.61023691117764 in 0.64453125 secs with 649 tries

Average of 5.61488044679165 in 0.47265625 secs with 644 tries

Average of 5.6125330966711 in 0.71875 secs with 923 tries

Average of 5.60884647786617 in 3.671875 secs with 2388 tries

Average of 5.6148922008276 in 3.62890625 secs with 2456 tries

Average of 5.61106038510799 in 0.70703125 secs with 614 tries

<tbody>
</tbody>

Once you start down a road seeking a way out, it is difficult to turn back on yourself!

Well done to Dante for sorting it

 

[DanteAmor]

YESSS Dante! That's worked perfectly. I knew there would be a simple macro for this, I must get round to learning how to do this.

I'm glad to help you. Thanks for the feedback.