Probability to turn i into range

[davegugg]

I am trying to create a procedure to create a set of numbers based on percentages, and I just don't have the probability knowledge to get this:

I will be iterating from i to some number (lets say 100).
I have the following table:

Please Login or Register  to view this content.

How can I go through i = 1 to 100 and make sure each i has a 5% chance of being 1-49,999, a 15% chance of being 50,000 - $99,999, ect.

Thanks

[shg]

You want a random number that 5% of the time will fall 0 to 50K, 15% will fall 50 to 100K, ...?

[davegugg]

You got it, thanks!

[shg]

Using a formula,

Please Login or Register  to view this content.

The formula in A2:A1001 is

=INT(LOOKUP(RAND(), {0,5,20,65,85}/100, RAND() * {50,50,100,100,150} + {0,50,100,200,300}) * 1000)

The frequency formula at right shows the distribution for one particular set of numbers.

[davegugg]

Ok, I see how that lookup function works, very nice. I am looking to run quite a few of these in vba. Is there a vba method or function that works the same way?

This is considered a probability problem, right?

[shg]

Try this:

Please Login or Register  to view this content.

[davegugg]

Wow, outstanding!

I'm actually a math major but never took a probability class (took two optimization research classes instead, which is how I know the Solver Add-In so well). I had to do a little research on wikipedia to understand how your function works, but I have the hang of it. You must have some formal education in math, where'd you go to school?

Thanks a lot, you deserve 10 reputation adds for that!

[shg]

You're welcome.

Grab it again, I made a change to accommodate varying base for the arrays.

Actually -- stand by -- both versions have an insidious error.

EDIT: OK, fixed.

[davegugg]

Got it, thanks