Forum Discussion
Time Between Purchases Issues
Hello,
I have a dilemma that is over my head. I will try to explain it below and I thank you all in advance for your support with this.
I have purchase history of companies in the sample data in the attached file. The data consists of total purchase amounts per month. However, there may be duplicates of a company in a different row (and that is OK).
What I need to find out is as follows:
0. The number of purchases.
1. The average time between purchases. (When calculating this, I consider the activity for duplicates over a month as a combined activity (i.e., if there is no purchase that month for both, that counts as one month without purchase.)
2. The standard deviation of the time (months) between purchases. (The duplicates rule apply here as well.)
Thank you in advance for your time and assistance!
I have achieved the formulas only with some new Excel functions like LET(), FILTER(), BYCOL() and DROP(). I hope you can already use them. Otherwise you can use Excel for the web. It should work there.
=LET(a,FILTER(COLUMN($C:$Z),BYCOL($C$3:$Z$7,LAMBDA(in,SUMIF($B$3:$B$7,$B3,in)))>0,"")-2,IFERROR(AVERAGE(DROP(a,,1)-DROP(a,,-1)),"One off purchase"))Try it out and see if it works for you.
4 Replies
I have achieved the formulas only with some new Excel functions like LET(), FILTER(), BYCOL() and DROP(). I hope you can already use them. Otherwise you can use Excel for the web. It should work there.
=LET(a,FILTER(COLUMN($C:$Z),BYCOL($C$3:$Z$7,LAMBDA(in,SUMIF($B$3:$B$7,$B3,in)))>0,"")-2,IFERROR(AVERAGE(DROP(a,,1)-DROP(a,,-1)),"One off purchase"))Try it out and see if it works for you.
- Thank you, dscheikey, very much for the solution. This is exactly what I was looking for!
Are you open to rearranging how you collect and array your data?
The reason I ask is that you're already doing some of the work that Excel would do for you. It's not unusual for people new to Excel to think they're making it easier by summarizing raw data (as you've done, for example, in just having the data as of the first of each month; I can't imagine that you actually DID those purchases on the first of each month.)
If you were to keep the raw data as your bank/brokerage undoubtedly has it, by specific date and amount, have a raw data arrayed in columns like this
Date -- Company -- Amount
Then Excel could readily produce the summary you've done (via Pivot Table) and formulae could be written that would give you the other results you're doing.
Let Excel do the heavy lifting for you. Do you have that raw data that we could work with?
I've attached an example where I rearranged some of the data just to illustrate what's possible. You'll notice that the rows of the raw data (columns A through C) are in date order, with the two companies mixed in as events occurred. I didn't eliminate the months with zero, though I could have; by definition, they're "non-events"
So I used the FILTER function in cell F4 to summarize the history for ABC Company (you could enter the name XYZ Company in G1 to see what happens)..... and that single FILTER function pulls up all the relevant rows. I then wrote a single formula in column I that calculates the interval in months between transactions beginning in the second row.
You could readily use COUNTIF to see how many times you made purchases in ABC --even more accurate if your data base had ALL of the actual purchases, i.e., raw data. And I'm sure you could figure out standard deviation as well.
But this is easier to do if you let Excel do the heavy lifting by leaving the raw data "unprocessed"
- Thank you for the detailed explanation and your shot at solving the issue, mathetes.
The data is raw data. The date format might have misled you. I am using monthly figures; nothing deeper is required here.
