Excel 2007 : RANDBETWEEN with probability of being within a range

**jcbarr82** · 07-18-2010, 09:14 AM

So I have the values 45 through 90. I can easily generated a number in that range with RANDBETWEEN(45,90)

What I want to do is generated a random number in that range with the probability of it being within a smaller range inside.

I want the number to fall between 45-90 with the following probabilities;

45-50 = 10%
51-65 = 30%
66-75 = 50%
76-90 = 10%

Anyone know how to do this easily?

**JBeaucaire** · 07-18-2010, 09:36 AM

1) Create a list in column A with 45-50 and 76-90 listed once
2) Add 51-65 to the list three times
3) Add 66-75 to the list 5 times
4) Randomize the list so the numbers are in no specific order
5) In column B, add this formula down the whole table:
=RAND()

Now you have a key column A of values to choose from and a random column B of precision decimal values that changes constantly.

In D1 put this formula:
=INDEX(A:A, MATCH(SMALL(B:B, 1), B:B, 0))

Now you will get a random value from column A in D1 with the probability ratio you asked for.

**jcbarr82** · 07-18-2010, 12:57 PM

Any way to do this just with a formula? I ask this because I want to be able to easily and quickly change the ranges and probabilities without having to redo a whole column of numbers.

Any way to do this with a macro or something of that nature?

**jcbarr82** · 07-18-2010, 01:05 PM

By the way, thanks for your work as well. I don't mean to sound ungrateful about it at all. It works very well.

**daddylonglegs** · 07-18-2010, 01:37 PM

You could use this formula

=CHOOSE(MATCH(RAND(),{0,0.1,0.4,0.9}),RANDBETWEEN(45,50),RANDBETWEEN(51,65),RANDBETWEEN(66,75), RANDBETWEEN(76,90))

**jcbarr82** · 07-19-2010, 09:49 AM

Wonderful. Looks like it does exactly what I want. The only question that I have, and it may be a stupid one, is where did you get these values;

{0,0.1,0.4,0.9}

Thanks.

**daddylonglegs** · 07-19-2010, 11:44 AM

The array of values will always start with zero and then the other figures are cumulative percentages, so because you want 10%, 30%, 50% and 10% then 0.1 represents the first %, i.e. 10% and then 0.4 is 10%+30% and 0.9 is 10%+30%+50%

so if you wanted the split to be 20%, 30%, 5% and 45%, for example, you'd use {0,0.2,0.5,0.55}