Finding the difference between more than 2 dates with a matching name from a different column

Hi All,

I've been searching for a way to calculate the average difference between order dates for each of my customers. In my data I have one sheet with all my customers and their orders.

Currently I have a second sheet that I have done the UNIQUE function to to filter through my sales sheet and give me each customers name. I want to use this individual name to build a formula that will only pull the dates for the specific customer and give me the difference between the order dates, so I have the average amount of time between orders.

I've attempted IF's, VLOOKUP's along with the DATEDIF function, however it looks like it's only good for 2 dates.

Is there a way for me to get the difference with more than 2 dates based off if the customer is as an example Customer 1?

Example data:

Account	Purchase Date
Customer 1	1/2/2023
Customer 2	1/15/2023
Customer 3	1/29/2023
Customer 2	4/2/2023
Customer 4	4/3/2023
Customer 1	4/6/2023
Customer 3	4/17/2023
Customer 4	6/20/2023
Customer 5	6/30/2023
Customer 2	7/12/2023
Customer 1	7/20/2023
Customer 4	7/30/2023
Customer 3	8/20/2023

Thank you all for your help!

Excel

Formulas and Functions

Patrick2788

Aug 24, 2023

ccolletti

To obtain for "Customer 1":

=LET(
    dates, FILTER(Purchase, Account = "Customer 1"),
    diff, IFERROR(DROP(dates, 1) - dates, ""),
    r, ROWS(diff),
    IF(r = 1, "-", AVERAGE(diff))
)

To do all 5 in one shot:

=LET(
    uAccount, SORT(UNIQUE(Account)),
    AvgDays, LAMBDA(a, v,
        LET(
            dates, FILTER(Purchase, Account = v),
            diff, IFERROR(DROP(dates, 1) - dates, ""),
            r, ROWS(diff),
            result, IF(r = 1, " - ", AVERAGE(diff)),
            VSTACK(a, HSTACK(v, result))
        )
    ),
    REDUCE({"Account", "Avg days"}, uAccount, AvgDays)
)

REDUCE 8.24.xlsx21 KB

=LET( uAccount, SORT(UNIQUE(Account)), AvgDays, LAMBDA(a, v, LET( dates, FILTER(Purchase, Account = v), diff, IFERROR(DROP(dates, 1) - dates, ""), r, ROWS(diff), result, IF(r = 1, " - ", AVERAGE(diff)), VSTACK(a, HSTACK(v, result)) ) ), REDUCE({"Account", "Avg days"}, uAccount, AvgDays) )

Forum Discussion

Finding the difference between more than 2 dates with a matching name from a different column

Share

Resources