Generating a RANDBETWEEN with a probability
- Thread starter KD Corrie
- Start date
Hi KD Corrie, and welcome to the forum. 
I'm not sure what exactly you're asking. When you generate a random number, it's a single data point. It doesn't really have a probability associated with it, other than at that moment, there's a 100% change it is what it is. To get a bell shaped curve requires multiple data points. But, if you're then creating a defined curve, you're no longer random. I suppose you could do something like:
=CHOOSE(RANDBETWEEN(1,4),160,250,250,310)
and that would give you values fitting your requirements?
I'm not sure what exactly you're asking. When you generate a random number, it's a single data point. It doesn't really have a probability associated with it, other than at that moment, there's a 100% change it is what it is. To get a bell shaped curve requires multiple data points. But, if you're then creating a defined curve, you're no longer random. I suppose you could do something like:
=CHOOSE(RANDBETWEEN(1,4),160,250,250,310)
and that would give you values fitting your requirements?
If you wanted to use a Normal distribution
You can loosely back calculate the parameters based on your data
You know the mean is 250
In your case if you want 25% < 160 and 25% > 310
this is uneven as 160 is 90 less than 250 and 310 is only 60 more than 250
If we use an average of +/-75 then
If you lookup some stats on a Normal Distribution you will see that 50% of the value fall within +/- 0.675 SD
So you want 0.675 x SD = 75
so 1 SD = 110
Refer: http://sphweb.bumc.bu.edu/otlt/MPH-Modules/BS/BS704_Probability/BS704_Probability10.html
So you now have a Mean 250 and a rough Standard Deviation of 110 to use in your calc's
You can loosely back calculate the parameters based on your data
You know the mean is 250
In your case if you want 25% < 160 and 25% > 310
this is uneven as 160 is 90 less than 250 and 310 is only 60 more than 250
If we use an average of +/-75 then
If you lookup some stats on a Normal Distribution you will see that 50% of the value fall within +/- 0.675 SD
So you want 0.675 x SD = 75
so 1 SD = 110
Refer: http://sphweb.bumc.bu.edu/otlt/MPH-Modules/BS/BS704_Probability/BS704_Probability10.html
So you now have a Mean 250 and a rough Standard Deviation of 110 to use in your calc's
You guys have been great.
I used Luke's recommendation however, I don't get any RANDOMNESS in the numbers I get 25% of the time I get 160, 50% of the time I get 250 and another 25% of the time I get 310. I need to generate greater variability in the random numbers. What I'm doing is running a Monte Carlo simulation on various clinic/hospital location that demonstrate this variability in demand historical of supply orders. However I need to generate random numbers which are about 25% ~160, 50% ~ 250, and 25% of the time are ~ 310 etc. I'll drop my spread sheet for your review. I have 8 locations that historically demonstrate various demand signals. After I have this model built, I can suggest improvements to our current supply chains.
Thanks, KD
I used Luke's recommendation however, I don't get any RANDOMNESS in the numbers I get 25% of the time I get 160, 50% of the time I get 250 and another 25% of the time I get 310. I need to generate greater variability in the random numbers. What I'm doing is running a Monte Carlo simulation on various clinic/hospital location that demonstrate this variability in demand historical of supply orders. However I need to generate random numbers which are about 25% ~160, 50% ~ 250, and 25% of the time are ~ 310 etc. I'll drop my spread sheet for your review. I have 8 locations that historically demonstrate various demand signals. After I have this model built, I can suggest improvements to our current supply chains.
Thanks, KD
Jake Collins
Active Member
Dear KD corrie
what about a formula along these lines?
=CHOOSE(RANDBETWEEN(1,4),RANDBETWEEN(1,210),RANDBETWEEN(210,280),RANDBETWEEN(210,280),RANDBETWEEN(280,340))
what about a formula along these lines?
=CHOOSE(RANDBETWEEN(1,4),RANDBETWEEN(1,210),RANDBETWEEN(210,280),RANDBETWEEN(210,280),RANDBETWEEN(280,340))
