Generate a random number based on three columns of various probabilities

[javajoe]

I want to generate a random number between 1 and X (where X is a dynamic number of varying length/range) in one of three columns (obviously using RANDBETWEEN) but then controlled by a manually-driven weighting factor assigned to each column I can change at any time.

So far example, if A1 is 70%, B1 is 20%, and C1 is 10%, then generally speaking 70% of the time I want it to give me a random number from 1-5 from column A, 20% of the time I want to return a number between 1-9, and roughly 10% of the time I want it to randomly return a number between 1-3.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]70%
[/TD]
[TD]20%
[/TD]
[TD]10%
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance!

 

[Marcelo Branco]

Re: Need to generate a random number based on three columns of various probabilities

Excellent thank you Marcelo Branco! Close enough to get me rolling thank you everyone!

You are very welcome. Glad to help.

M.