0

Say I have three items, each with a designated frequency from 1-3, with 1 being 'Low' and 3 being 'High'.

Item A - 1  
Item B - 2  
Item C - 3

On recalculating the sheet, I want a cell to display a random one of those items, but according to their frequency.

(i.e. if I calculate 6 times, I would expect Item A to show up once, Item B to show up twice, Item C to show up thrice.)

Ideally, I'd also force the expected value each time by making the random numbers used unique (e.g. every number from 1-6 appears exactly once).

I have a working solution but it's terribly time-consuming to set up in future worksheets and I barely understand it looking back on the code - can anyone think of a neat way to do it?

(My solution involved summing the frequencies to establish boundaries for each item [e.g. Item C's boundaries would be 4-6] then comparing a random number to that boundary and searching for the appropriate item)

Jack Nagy
  • 3
  • 1
  • 5
  • here's **all the code** for c# .. just copy and paste http://stackoverflow.com/a/33991225/294884 (Note that it's pointless bothering to sum them beforehand, the processing is totally trivial. In the extremely unusual case where you had to generate trillions of them or something, you'd take an entirely different approach.) – Fattie Nov 30 '15 at 04:05

1 Answers1

1

In statistics, this is called a Bernoulli distribution (see Wikipedia http://en.wikipedia.org/wiki/Bernoulli_distribution). It's quite easy to implement in an Excel sheet: you draw from a standard uniform distribution (using function RAND) and if the draw is less then 1/6 (for item A) then result is 1, else result is 0. For items B and C, use the same logic except that probability is 2/6 and 3/6. You can test the result by repeating this for all items say a 1000 times and then calculate the average. The result should be very close to your expected probabilities. The formula to use for a given item is:

=IF(RAND()<=A$1;1;0)

where A1 contains the probability (e.g. 1/6 for item A)

Philippe.H
  • 302
  • 4
  • 13