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!!!!
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.
Last edited by NickyC; 08-07-2013 at 04:24 AM. Reason: typos
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks