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

 

[BeepBeep]

Hi Gaz,

But when building a Pivot-table I could not group them in to bins of days to see the no of Customers in each bin. I do not know the reason why. But the column is a numerical column. Still I could not throw the values in to the bins. Any reasons...?

BeepBeep

 

[gaz_chops]

Hi, not sure I understand what you mean by "bins of days"?

Do you mean like (13,15,17 etc are number of days)

[TABLE="width: 341"]
<tbody>[TR]
[TD]Count of cust[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]43[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[BeepBeep]

Hi Gaz,

Generally in the Pivot Table environment, we can group any numerical column in to classes. i.e. Running dates into weeks, months, years; Running time into Hours, Minutes, Seconds..or even days into intervals of days.

In the same way, I named the column as Frequency. I tried to use this column in my Pivot table as a row label after grouping them in to different intervals. But it did not happen as I expected as Excel could not group the formula column for some reason saying "Can not group that selection".

If I could group the Frequency column, I can use my Customers column (on count) as values in the Pivot-table. See below.

Frequency(days) NoOf Customers

1 - 10 --------------20
11-20 --------------10
21-30 --------------12


So I can see How many customers are there n each class. Is it clear now or you need any other information on this?
BeepBeep

 

[gaz_chops]

Do you mean like below? Seems to work ok!

[TABLE="width: 392"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Count of days[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Group1[/TD]
[TD]Group2[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Row Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]fred[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]jen[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]jim[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]jon[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[BeepBeep]

Hi Gaz,

I know I am annoying you a lot, but I am under extreme pressure in my office. This is my last requirement in the process of Reporting to my management.

In the Pivot Table, I want to use THAT column with the formula you solved (I named that column as Frequency which is a numerical column) as my Row Label, and my Customers names as my Values field (which shows count).


If I can group that column (which is in the Row labels) in to different intervals say 10-20 or 20-30, I can see how many Customers are there in that interval.

Normally we can group any numerical field into intervals, right? Same way I tried to group the column which is in the Row label, but it could not grouped. That's where the problem. Thank you so much for your attention.

BeepBeep

 

[gaz_chops]

Hi, not annoying at all.
I am using excel for mac so can't replicate the problem!
Might be best to post a new topic asking for help.

Sorry.
Gaz

 

[chicagocomputerclasses]

Because you have blanks in that column you won't be able to group the numbers.
You can modify gaz_chops formula to output 0s instead of blanks. Just be careful when grouping in Pivot Table, you MUST change start to 1 and then filter the <1 results.

=IFERROR(B2-INDEX($B$1:$B1,MATCH(2,1/($A$1:$A1=A2))),0)

Also note that this formula assumes that each next row you have has a DATE larger then any DATE in preceding rows. If that's not the way your dataset is sorted it's not going to work properly.

 

[BeepBeep]

That is what I was going to do some formatting and filling all blanks with "0"s. At last it was done. Thank you so much Gaz and ChicagocomputerClasses.

 

[gaz_chops]

Never thought of blanks having an effect!

"Also note that this formula assumes that each next row you have has a DATE larger then any DATE in preceding rows. If that's not the way your dataset is sorted it's not going to work properly."

I don't believe that is true, as long as the dates for each customer are in ascending order it should work fine!

 

[gaz_chops]

You're welcome.