Drawing a random number from a defined distribution/defined likelihood distribution

[Pasi84]

Hi there,

I'll be working into the night (it's around 23.41 already) with this, so any help would be much appreciated and save some of my sleep!

My issue is the following: How can I draw a value at random from a defined likelihood distribution? I have data on the daily purchases of a product around the year. There might be several (up to a tens or hundreds) purchases per day, and some days might not have purchases at all.

What I want to do, is for each client, draw and create a new variable, a so called random, "shadow" purchase date. This date should be drawn out of a distribution that matches the actual ones, but this shadow date can any of the dates around the year when at least 1 purchase occured. So if february 14th has 100 times more purchases than february 13th, I would like 14th to have 100 times the likelihood of being drawn.

The only method I could come up with is to create a range where I enter each possible date as many times as actual purchases occured (so e.g. 14.02.2012 would have a 100 cells), and then use randbetween. But given the data, this is far too laborious to be feasible.

Any other methods for doing this? Seems like such an elementary thing to do, but my skills or qoogling did not reveal a method.

Help is much appreciated!

[shg]

       -----A----- ---B--- -----C----- --------------------D---------------------
   1      Date     # Purch    Prob                                               
   2   01 Feb 2012      15       0.000 C2 and down: =SUM(B$1:B1)/$B$19           
   3   02 Feb 2012      16       0.087                                           
   4   03 Feb 2012       7       0.179                                           
   5   04 Feb 2012      17       0.220                                           
   6   05 Feb 2012      20       0.318                                           
   7   06 Feb 2012       1       0.434                                           
   8   07 Feb 2012      10       0.439                                           
   9   08 Feb 2012       5       0.497                                           
  10   09 Feb 2012      14       0.526                                           
  11   10 Feb 2012       6       0.607                                           
  12   11 Feb 2012      16       0.642                                           
  13   12 Feb 2012      11       0.734                                           
  14   13 Feb 2012      13       0.798                                           
  15   14 Feb 2012       8       0.873                                           
  16   15 Feb 2012       2       0.919                                           
  17   16 Feb 2012      12       0.931                                           
  18                                                                             
  19                   173 05 Feb 2012 C19: =INDEX(A2:A17, MATCH(RAND(), C2:C17))

[Pasi84]

Works. Thanks a lot for this. I forgot to think about approaching it through the cumulative distribution. Smart move

[shg]

The universal method to duplicate a distribution.