JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,676
- Office Version
- 365
- Platform
- Windows
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.
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?
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.
VBA Code:
=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?