How often my customers are buying from me?

[BeepBeep]

Hi All,

Good morning..!

I have an Excel data with columns OrderDate, Customer, Sales. I want to use this data for a Pivot table. So I need a formula in a column which gives me information about "How often the customers are buying my products".

Some examples of transactions.

A. Mr. John bought Product-A on July 12, 2012.
B. John bought Product-B on Aug 24, 2012.
C. Again he bought Product-C Dec 25, 2012.

In the row of Txn-B, I need a difference between the Date of Txn-A and Txn-B i.e. 43 days
In the row of Txn-C, I need a difference between the Date of Txn-B and Txn-C i.e. 123 days and it goes on. Is it possible with in Excel or am I asking too much??

Thanks
BeepBeep

 

[gaz_chops]

If they are date format just say date B minus Date A etc.

 

[BeepBeep]

Hi gaz_chops,

Thanks for the reply. What I need is; If the customer name matches with Txn-A and Txn-B or Txn-B and Txn-C, then I need the no of days between both the Txns i.e days between Txn B to Txn A and Txn C to Txn B. What should be my formula?

BeepBeep

 

[gaz_chops]

Assuming your customer names are in Col A & Dates in Col B, try

MUST be entered with Ctrl, Shift & Enter
=IFERROR(B2-INDEX($B$1:$B1,MATCH(2,1/($A$1:$A1=A2))),"")

 

[BeepBeep]

Hi gaz_chops,

It shows blanks for all of the records at my end. What about your output? What are you trying to do?

BeepBeep

 

[gaz_chops]

You must enter with Ctrl, Shift & Enter

 

[gaz_chops]

My example

[TABLE="width: 203"]
<tbody>[TR]
[TD]cust[/TD]
[TD]date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD="align: right"]01/01/12[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]jon[/TD]
[TD="align: right"]01/01/13[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]jen[/TD]
[TD="align: right"]12/12/12[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD="align: right"]31/01/12[/TD]
[TD="align: right"] 30.00[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD="align: right"]15/02/12[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD="align: right"]28/02/12[/TD]
[TD="align: right"] 28.00[/TD]
[/TR]
[TR]
[TD]jon[/TD]
[TD="align: right"]14/01/13[/TD]
[TD="align: right"] 13.00[/TD]
[/TR]
[TR]
[TD]pete[/TD]
[TD="align: right"]14/01/13[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]jon[/TD]
[TD="align: right"]31/01/13[/TD]
[TD="align: right"] 17.00[/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD="align: right"]31/03/12[/TD]
[TD="align: right"] 32.00[/TD]
[/TR]
[TR]
[TD]jen[/TD]
[TD="align: right"]31/01/13[/TD]
[TD="align: right"] 50.00[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD="align: right"]01/03/12[/TD]
[TD="align: right"] 15.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[BeepBeep]

gaz_chops,

I did enter Ctrl-Shift-Enter, but it shows blanks. What does it mean? But I have repeated Customer names in the data, still it did not give the days between the dates. Thank you.

BeepBeep.

 

[BeepBeep]

Hi gaz_chops,

It is working fine...Thank you so much.. It is in he Manual calculation mode..Thanx

BeepBeep

 

[gaz_chops]

You're welcome.

Gaz