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 summ...
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.
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.
- lol wow, this is awesome. thanks again. very sharp!