How to generate random numbers with a specific mean?

[JenniferMurphy]

I need to generate a set of random numbers with a target mean. For example, random numbers on (0,100) with a mean of 65.

I found a bunch of websites that talked about generating random numbers with a set mean and std dev using the Norm.Inv function, but those numbers would follow a Normal distribution and it wasn't clear to me how I would get them to stay within the (0-100) range and use all of it. I suppose a Normal distribution would work if there is one that goes to zero at 0 and 100 and not beyond. Is there?

I came up with this formula which seems to work.

=IF(RANDBETWEEN(0,100)<65,RANDBETWEEN(65,100),RANDBETWEEN(0,65))

It does seem to tend toward the correct average. I wasn't sure if the "<65" should be "<=65" and/or if one of the ranges should be (66,100) or (0,64).

Any comments? Is there a better way?

 

[jasonb75]

Have a look at the attachment in the last reply here:- Randbetween with fixed sum in a flexible formula

 

[JenniferMurphy]

That's interesting, but I can't figure out what it is doing.

The equations in columns C & D both refer to cell C6, which is a column header.

The macro code is beyond my understanding at this time, but I don't see the mean mentioned anywhere.

Maybe I should get an account and post my questions there.

In the meantime, is there anything wrong with my solution.

 

[jasonb75]

In the meantime, is there anything wrong with my solution.

Your method is is a random selection from a random selection which effectively means that you have a random chance of getting the correct result.

edit:- a quick test of your formula with a set of 20 random numbers gave mean averages between 37.78 and 83.36 when it was recalculated approx 100 times, showing very little in the line of consistency.

but I don't see the mean mentioned anywhere.

That is set in the sheet, sorry I should have mentioned that. The SUM in B1 should be the required mean multiplied by N (the size of the set). So for 10 random numbers between 0 and 100 that have a mean of 65 you would set the sum to 650 (65 *10).

 

[JenniferMurphy]

Your method is is a random selection from a random selection which effectively means that you have a random chance of getting the correct result.

edit:- a quick test of your formula with a set of 20 random numbers gave mean averages between 37.78 and 83.36 when it was recalculated approx 100 times, showing very little in the line of consistency.

I wrote a little function to test my method. The results show that it does, in fact, work fairly well.

Here's the code:

    Function RandTgtMean(pMin As Double, pMax As Double, pTgtMean As Double) As Double

Dim msg As String
Dim iPause As Long: iPause = 1
Dim Sum As Double:  Sum = 0
Dim Mean As Double: Mean = 0
Dim N As Long:      N = 0

Do
  N = N + 1   'Counter
  'Generate a random number on the specified interval [pMix, pMax]
  'If it's less than the target mean,
  If Application.WorksheetFunction.RandBetween(pMin, pMax) < pTgtMean Then
    'Add a random number above the target mean
    Sum = Sum + Application.WorksheetFunction.RandBetween(pTgtMean, pMax)
  Else
    'Else, add one below the target mean
    Sum = Sum + Application.WorksheetFunction.RandBetween(pMin, pTgtMean)
  End If
  Mean = Sum / N              'Calculate the mean
  'Debug.Print N & " " & Mean
  If N Mod iPause = 0 Then    'If it's time to pause, ask if we continue
    msg = Format(N, "#,##0") & "  " & Format(Mean, "0.00") _
        & vbCrLf & vbCrLf & "Continue?"
    If MsgBox(msg, vbYesNo) <> vbYes Then Exit Do
  iPause = iPause * 10        'Increase pause frequency by 10x
  End If
Loop
    
RandTgtMean = Mean

End Function

I didn't add any error checking. All parameters must be integers, pMax must be > pMin, & pMin <= pTgtMean <= pMax

 

[bebo021999]

Not understand 100% what is your requirement, but if you want to get the two RANDBETWEENs, with 50% occurance change to each:
=CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(0,65),RANDBETWEEN(66,100))

 

[JenniferMurphy]

Not understand 100% what is your requirement, but if you want to get the two RANDBETWEENs, with 50% occurance change to each:
=CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(0,65),RANDBETWEEN(66,100))

As I said in the first line of this thread,

I need to generate a set of random numbers with a target mean. For example, random numbers on (0,100) with a mean of 65.

If the target mean is 50 on a range of [0,100], your formula might do it, but I need it to work for any min,max range and target mean in that range.

My function seems to do what I need, unless someone can show me where it goes wrong.

 

[bebo021999]

This formula
=CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(0,65),RANDBETWEEN(66,100))
number from 0 to 65 has change of 50% to appear, also number from 66 to 100 has 50%...

I am still no understand what "targer mean", "Mean of 65" is. Could you give a sample?

 

[JenniferMurphy]

Here are some results using my code above:

Min = 0 Max = 100 Tgt Mean = 65
1 75.00
10 66.10
100 65.35
1,000 64.38
10,000 64.85
100,000 64.81

Min = 0 Max = 100 Tgt Mean = 97
1 97.00
10 95.20
100 96.87
1,000 96.45
10,000 96.54
100,000 96.56

Min = -39 Max = -15 Tgt Mean = -25
1 -18.00
10 -25.40
100 -25.46
1,000 -25.63
10,000 -25.19
100,000 -25.26

Min = 17 Max = 17 Tgt Mean = 17
1 17.00
10 17.00
100 17.00
1,000 17.00
10,000 17.00
100,000 17.00

Min = 0 Max = 10000 Tgt = 666
1 47.00
10 296.00
100 860.94
1,000 710.79
10,000 651.93
100,000 668.20

Does that qualify as "working"?

 

[JenniferMurphy]

I am still no understand what "targer mean", "Mean of 65" is. Could you give a sample?

I am looking for a way to generate a set of random numbers on some range [Min,Max] that have a specific mean, say 65.

Do my examples above help? Or you can run the code yourself.