Help using if functions, rand() & inequalities all in one equation

[CassAsvestas]

I am trying to form an "if" function. Given set probabilities a certain number of magazines are sold. In this situation a randomly generated number (rand()) is checked against a probability range from (0-1.0) generates a given value for those specific ranges (0-.5 is 50, .5-.15 is 75 ... for example), but keeps running the test until it gets an answer that is less then or equal to 100 magazines. In this case the .15-.4 ptobability range.

The closest I have gotten is:

=lookup(rand(),$J$11:$J$16,$H$11:$H$16)

When I do

=if((lookup(rand(),$J$11:$J$16,$H$11:$H$16))<=$J$18,B5,lookup(rand(),$J$11:$J$16,$H$11:$H$16))

It still doesn't come up with the values <= J18

I'd appreciate any assistance or advice you can provide.

 

[oldbrewer]

prob of selling 1 mag .001
...................5............... 0.01
....................20............ 0.1
....................40............ 0.3
....................120.............. 0.4
....................150..............0.1
....................170............... .05
.....................200............. 0.01

make a lookup table or use the normal distibution table

 

[CassAsvestas]

prob of selling 1 mag .001
...................5............... 0.01
....................20............ 0.1
....................40............ 0.3
....................120.............. 0.4
....................150..............0.1
....................170............... .05
.....................200............. 0.01

make a lookup table or use the normal distibution table

Thank you, but I do have a lookup table. It has
probability # of magazines
0 50
.05 75
.15 100
.40 150
.55 175
.80 200
1.0 210

We want to limit the output to be equal to or less then 100 magazines, or <=175, or <=200.

So if the random number(probability) exceeds the limit(# of magazines), it regenerates a random number until it fits the constraint.