Random numbers between two values with condition

T

techfreak

New Member
Joined
Dec 17, 2013
Messages
45
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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
You could do this:

Code: 
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!
 
Last edited:
Upvote 0
techfreak said:
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
Click to expand...

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:

Code: 
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
 
Upvote 0
Or using an array is quick!

Code: 
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
 
Upvote 0
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. :beerchug:

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

Thanks again guys
 
Upvote 0
Ha! The original came out like this in tests:
[TABLE="width: 500"]
<tbody>[TR]
[TD="width: 64"]Average of 5.59987054288387 in 10.12109375 secs with 326 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61696160256863 in 4.1015625 secs with 185 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.60427415788174 in 13.38671875 secs with 590 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.59763451039791 in 9.53125 secs with 411 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.60301578462124 in 5.890625 secs with 257 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61176562726498 in 5.375 secs with 233 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.60131421506405 in 5.90625 secs with 268 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.6197777312994 in 1.49609375 secs with 48 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61121941030025 in 1.97265625 secs with 90 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.59769466340542 in 0.015625 secs with 17 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.60658693730831 in 5.05859375 secs with 219 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61038971364498 in 9.05859375 secs with 388 tries[/TD]
[/TR]
[TR]
[TD]Changed Average setting to +/- 0.05[/TD]
[/TR]
[TR]
[TD]Average of 5.60971558511257 in 6.29296875 secs with 279 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61160107076168 in 3.8515625 secs with 145 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61145794808865 in 4.78125 secs with 215 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.60723963201046 in 28.90625 secs with 1293 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61169302880764 in 24.81640625 secs with 1055 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.6061140102148 in 11.640625 secs with 509 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61188939034939 in 63.609375 secs with 2471 tries[/TD]
[/TR]
</tbody>[/TABLE]

Using the array:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Average of 5.61055980145931 in 0.52734375 secs with 191 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.60867915570736 in 1.37890625 secs with 736 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.60868814408779 in 0.30859375 secs with 91 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61208851754665 in 0.1328125 secs with 362 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.6137078088522 in 2.61328125 secs with 1786 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61319685399532 in 0.046875 secs with 1019 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.60553322255611 in 0.44921875 secs with 204 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61214867055416 in 5.88671875 secs with 5088 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61311319291592 in 2.94921875 secs with 2229 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61340427815914 in 2.8125 secs with 1971 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61186104238033 in 2.9921875 secs with 2232 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61213415086269 in 0.14453125 secs with 285 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61149874150753 in 0.36328125 secs with 367 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61023691117764 in 0.64453125 secs with 649 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61488044679165 in 0.47265625 secs with 644 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.6125330966711 in 0.71875 secs with 923 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.60884647786617 in 3.671875 secs with 2388 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.6148922008276 in 3.62890625 secs with 2456 tries[/TD]
[/TR]
[TR]
[TD]Average of 5.61106038510799 in 0.70703125 secs with 614 tries[/TD]
[/TR]
</tbody>[/TABLE]

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 :beerchug:
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Can I generate a random order but with two inputted numbers?
Replies
5
Views
695
Peter_SSs
Peter_SSs
N
  • Question Question
Column with Min/Average values at the end of Pivot Table
Replies
0
Views
127
Nurces
N
C
  • Question Question
Finding all combinations to a summed value
Replies
9
Views
338
cerberus1845
C
J
  • Question Question
Why are there NO quotation marks in my variable. This is the reason why my if statement is not equal (FALSE).
2
Replies
14
Views
553
Jurg55
J
R
  • Solved
Complex formatting help please!
Replies
4
Views
257
Routus
R

Forum statistics

Threads
1,223,702
Messages
6,173,957
Members
452,539
Latest member
delvey

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