How to calculate days elapsed between transaction

[Jakenash]

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. 

[#2]

Can you please post few sample data (better to attach a sample excel file) and show your desired output.

[#3]

@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

[#4 · Accepted solution]

@Jakenash 

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))

[#5]

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.

[#6]

@Jakenash 

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.

[#7]

lol wow, this is awesome. thanks again. very sharp!