Average time between multiple dates (tied with a unique identifier)

[Shareit]

Hi,I have a database with 100,000(+) unique customer ID tied to their invoice dates (of purchasing)over a period of 3 years. I would like to understand the buying pattern of each customer by calculating the average time between their purchases (determined by their invoice dates. Sample as below:


Transaction Data:


ID Invoice Date (mm/yyyy)

AA 01/2013

AA 01/2013

BB 07/2011

BB 01/2011

BB 07/2010

BB 07/2010

BB 06/2010

BB 05/2010

BB 05/2010

BB 03/2010

CC 02/2010

CC 01/2010

CC 04/2010

CC 09/2012

CC 01/2013

DD 12/2012

EE 11/2012

EE 06/2012

EE 05/2012

FF 03/2012

FF 01/2013

… …

… …


Is there a straight forward formula where I can feed the "average time between invoice dates" into the table below (using the left column (ID) as a table of reference)?


Analysis:


ID Average Time between Invoice Dates (in days)

AA:

BB:

CC:

DD:

EE:

FF


…

 

[sdsurzh]

Try AVERAGEIFS (if you have 2007 excel)

 

[Shareit]

Thanks, but you know how to write the full formula - taking into the consideration the unique IDs?

 

[sdsurzh]

Can you please upload the workbook?

 

[Shareit]

Where can I upload the file? Do I have to park it outside like Dropbox, etc?

 

[Debraj]

Yes.. park it outside.. and provide us the KEY to check your vehicle..


http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Deb

 

[Shareit]

Here you go:


https://docs.google.com/file/d/0B1WgMhjadVZ4WWVmQ2lXZjZDWlU/edit?usp=sharing

 

[NARAYANK991]

Hi ,


Suppose we assume that your unique IDs are listed in a range K2:K8 as follows :

[pre]

AA_15841170
AA_15637893
AA_15638848
AA_15638855
AA_15638856
AA_15638860
AA_15639108

[/pre]
In L2 , enter the following formula , as an array formula , using CTRL SHIFT ENTER :


=(MAX(IF($B$2:$B$22=K2,$C$2:$C$22))-MIN(IF($B$2:$B$22=K2,$C$2:$C$22,99999999)))/COUNTIF($B$2:$B$22,K2)


I have assumed that your data is in columns B and C , from B2:C22.


Narayan

 

[Shareit]

Hi Narayank991, thanks, but the results don't look right (with negatives).

Just wondering why is there a '9999999' within the formula?

 

[NARAYANK991]

Hi ,


I went with your original sample data ; now that you have uploaded your workbook , I'll check it out , and see.


Narayan

 

[NARAYANK991]

Hi ,


Try this :


=(MAX(IF($A$3:$A$66=D3,$B$3:$B$66))-MIN(IF($A$3:$A$66=D3,$B$3:$B$66,99999999)))/COUNTIF($A$3:$A$66,D3)


Narayan

 

[Shareit]

Thanks Narayan,

The above formula looks 'logically right', but somehow did not elicit the right results.

And when drag it down the column, the results got even more bizarre. Any idea if we could rectify the formula ?

 

[NARAYANK991]

Hi ,


Can you indicate which result is bizarre ?


I assume you have entered the formula using CTRL SHIFT ENTER.


Narayan

 

[Shareit]

Hi, sorry for this late response, as I have been away.


If you could refer to this link below: (https://docs.google.com/spreadsheet/ccc?key=0AlWgMhjadVZ4dFJYSWVCTDAwMEJHSzJLT0tWWEM2Vnc&usp=sharing)


Then you'll know what I mean.

The results doesnt look like its in days/weeks. Kindly advise.


Thanks!

 

[NARAYANK991]

Hi ,


Let me see , and I'll get back to you.


Narayan