# Assigning a zipcode in a simulation given the probability of having a bill in that zipcode

1. ## Assigning a zipcode in a simulation given the probability of having a bill in that zipcode

So I am new to Excel and have a problem I am not sure how to approach. I have a list of zipcodes each with a number of bills in each. So there is a certain percentage or probability that the bill will be in that zipcode. What I want to do is create a simulation that given a bill will assign it a zipcode given the list of probabilities. I will attach the spread sheet so you know what I am dealing with.

Thank you!!!!  Register To Reply

2. ## Re: Assigning a zipcode in a simulation given the probability of having a bill in that zip

Hi
I'm sure one of the forum Gurus could find a more elegant solution, but this formula seems to work

It generates a random number between 0 and 1, looks up that number in your cumulative probabilities column (D), and finds the corresponding zip code in column A. Because the values in column D are cumulative, it should retun zip codes according to the probability of their occurring.

Let me know if this works for you. ``Please Login or Register  to view this content.``  Register To Reply

3. ## Re: Assigning a zipcode in a simulation given the probability of having a bill in that zip

Hey so I gave it a try and it worked wonders! I initially had the problem that if the random # was under the value for the first zip code it would return a #N/A. I fixed this simply by adding a row before my data as zipcode 0 total 0. That did the trick and now I know how to solve this type of problem. What a great help! Solved!  Register To Reply