Nov 06 2022 07:17 PM
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.
Nov 06 2022 08:35 PM
Nov 07 2022 10:43 AM
@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 |
Nov 07 2022 11:20 AM
SolutionIf 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))
Nov 08 2022 08:21 AM
Nov 08 2022 09:21 AM
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.
Nov 14 2022 02:05 PM
Nov 07 2022 11:20 AM
SolutionIf 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))