Forum Discussion
Solved
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...
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) )
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)
)- Thank you so much Patrick2788! That worked for me!
I used the first one to get the average for each customer individually.- Glad it worked. You're welcome!
