Solved - Use Random Number to Derive # based on Probability Table

Question

Update I was able to derive the algorithm and implement it into excel. Thanks for the formula.

Something like: ((z-xlbound)/d(x)*d(y))+ylbound

See original sheet at end of post

Original Post

Given a set of cumulative probabilities in one column, and a set of their corresponding (sequential) values in another column.

Is there a way I can take a RNG that derives a value between 0 and 1 (that matches to the cumulative probabilities), and derive the matching sequential #?

I setup a spreadsheet with two tables (inside rectangles) that match a value to it's respective lowest/highest %.

I did solve it using rather convoluted method; but ultimately, I think I'm doing a frequency bin lookup. However, is there an easier way than doing a frequency bin lookup?

I read on wikipedia about "expected value" and how it can be derived using the probability times it's expected value... so I was wondering if something similar could be done when trying to derive any rng rather than "expected" aka "mean" value.

Here's a spreadsheet showing two tables, with two setups of %'s matched to values.

https://docs.google.com/spreadsheets/d/1fov6UjqKcc__shufhNE6uKkRxnsywMiqesLboxFtHdw/edit?usp=sharing

Answer 1

It seems you are wanting to generate (pseudo-)random values whose discrete distribution is specified by a given cumulative distribution function (CDF). The columns in each of your tables are $x$ and $F(x)$, where $F$ is the CDF of a random variable $X$. Now, if $U \sim \text{Uniform}(0,1]$, then $$\Bbb{P}(F(x_{i-1}) \lt U \le F(x_i)) = F(x_i) - F(x_{i-1}) = \Bbb{P}(X = x_i).$$

So, the following procedure will output values that are (pseudo-)randomly distributed with the CDF specified in the table:

1. Generate $U$.
2. Look in the $F(x)$ column and find the interval $(F(x_{i-1}), F(x_i)]$ that contains $U$.
3. Output $x_i$ (the larger of the two corresponding $x$-values).

Answer 2

I was able to post my own solution. I wasn't able to make much sense of how to implement said formula; however after a nights sleep, I was able to figure it out in a minimal number of steps.

((z-xlbound)/d(x)*d(y))+ylbound

edit: (f(x)-x(lbound))/d(x)*d(y))+y(lbound)

Answer is posted in formula format in the same spreadsheet posted.