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. Cur...

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

Forum Discussion

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

Resources