Forum Discussion
Solved
How to calculate days elapsed between transaction
Hello,
I work for a non-profit and am trying to get insight into how frequently each of our donors contribute financially. I have a donor register with names, dates, amounts. I currently am summarizing this information on a pivot table.
However, is there a way to find the average days elapsed between each giving transaction? It would need to be specific per donor.
It would be helpful to analyze how each of our donors individually gives, in order to help our future campaigns.
If you're on 365, this might be your solution:
=LET(filtered,FILTER($F$2:$F$21,$D$2:$D$21=I2),dates,COUNT(filtered),IF(dates>1,AVERAGE(DROP(filtered,-1)-DROP(filtered,1)),filtered))
If you're on 365, this might be your solution:
=LET(filtered,FILTER($F$2:$F$21,$D$2:$D$21=I2),dates,COUNT(filtered),IF(dates>1,AVERAGE(DROP(filtered,-1)-DROP(filtered,1)),filtered))- Wow, this is exactly what I was looking for. You're a wizard!
Do you mind explaining the formula? I can follow that you took the original list with PayerIDs, amounts, and dates and then create a unique column of PayerIDs from which your =LET formula calculated from.
What does "filtered", and "dates" and "DROP" mean? Thanks in advance.LET allows you to assign calculation results to names.
"Filtered" is the name used to refer to the below. FILTER obtains the date(s) for a given ID.
FILTER($F$2:$F$21,$D$2:$D$21=I2)"Dates" refers to the below. We're counting how many dates a given ID returns.
COUNT(filtered)The logic. If the number of dates is more than 1, drop 1 date at the end and subtract from it - all but the first date and then find the average of the differences found. If there's one date, show the date.
IF(dates>1,AVERAGE(DROP(filtered,-1)-DROP(filtered,1)),filtered)Essentially, this is the subtraction the above is doing:
The subtraction is done by making 2 staggered lists of dates so when the subtraction is done it will find the difference between dates. Then we average the difference.
- Can you please post few sample data (better to attach a sample excel file) and show your desired output.
Harun24HR thanks for your response.
I want to find the days between each transaction, per PayerID, for repeat donors.
This exercise seeks to find the frequency and ultimately average time between each transaction, per repeat donor.PayerID Amount Date Received 1458662946 51.1 11/4/2022 12167270467 200 11/4/2022 12131763519 110 11/3/2022 12131757642 25000 11/3/2022 7809555286 21 6/26/2022 7809555286 38 6/22/2022 7804539738 102.2 10/17/2022 7736255381 61.32 6/17/2022 7396511985 100 6/14/2022 7392399070 1000 6/12/2022 7392361055 40000 9/4/2022 7388445731 20.44 6/24/2022 7368787451 25 9/13/2022 7368787451 25 8/13/2022 7368787451 25.55 8/13/2022 7368787451 25 7/13/2022 7368787451 25.55 7/13/2022 7368787451 102.2 6/7/2022 7354195806 10 6/4/2022 7354184301 100 6/4/2022
