How can I use percentages to output a value based on probability?

Hi all, I've become stumped by this problem -

sample.JPG

What I want to do is have values appear in, say, C11:G11 based on the probabilities in the table. To explain what I mean, if it was the physical real world and I was looking for values for "A", it would be like putting 100 balls into a bag. 50 are marked 0, 38 are marked 25000, 9 are marked 50000, and 3 are marked 100000. I pull five balls out and put the values in row 11. And so on for B, C, D, etc.

I'm stumped. Everything I google tells me how to calculate probabilities but not how to use them.

Sample worksheet attached. Hope someone can help.

Cheers! :-)

Picky, but A in cell A1 corresponds to the labels in B1:N1 rather than to probabilities.

What you need are CUMULATIVE probabilities, but those are easy enough to calculate on the fly.

C12:

Formula:

Please Login or Register  to view this content.

That said, it'd be FAR MORE EFFICIENT to use another range to calculate the needed cumulative probabilities, say in P2:AB8, with 0 in P2, =P2+B2 in Q2, fill Q2 right into R2:AB2, then select P2:AB2 and fill down into P3:AB8. Then change the formula above to

C12:

Formula:

Please Login or Register  to view this content.

Wow! You're a genius! Thank you so much.

I don't understand how it works to make sure it's favoring the higher probabilities - but I tested that it is (by changing some cells to 100% and the rest in the row to 0%) and I can only shake my head in wonder.

I also made sure to move the A1 A down to A2.

One follow up question: I made a version last night and I was sometimes getting #NA! responses such as:

Did not find value '0.04246832255' in LOOKUP evaluation.

I did something to fix it, but now I've made a second version which appears to function exactly the same and I'm getting the error again. Can't for the life of me remember how I fixed it though!

Any thoughts?

The range or array of cumulative probabilities MUST BEGIN WITH ZERO. You don't provide your updated workbook, so I can only guess. However, if cumulative probabilities do begin at zero and are monotonically increasing, then LOOKUP(RAND(),range_or_array_of_cumulative_probabilities,labels) should always return an item from the 3rd argument.

Thank you! Yep, it was totally cos I hadn't included the column of zeros in the range.

Couldn't see the wood for the trees.