Calculating average time between orders (Gsheets)

[Ajanssen1993]

Hi all,

I've been struggling with getting a formula for a couple days now. I've been trying to visualize the time it takes for a customer to place a 2nd order, and then a 3rd order, and so on. Now I'm not a complete noob with Gsheets, but I'm definitely not a pro either, so I could use a little help.

- So what we have is a sheet with unique customer ID's in "A". Duplicates removed, so only the unique ID's.
- Then all orders by customers in "N", so these are all of the orders placed. Customers appear multiple times when they placed multiple orders.
- The date with each order in "O"
- And the frequency of customer (how often we see a customer in "N") in P (probably not necessary for this function)


I would love a function that searches for the given Customer ID from A, in row N, and gives the date of the 1st order in B, the 2nd order in C and the 3rd order in D (when multiple orders occur). I think it should be something with VLOOKUP but I can't seem to get it right. I've manually filled in the 1st column to give you an idea. The days between orders (G t/m J), I can handle.. ;-)


Thanks a lot!

[AliGW]

Welcome to the forum.

Please change your forum profile from MS to Google Sheets. Thanks.

[romperstomper]

Assuming your data is sorted in date order, you could clear your current results, then put:

=transpose(filter($O$2:$O,$N$2:$N=$A2))

in B2 and copy down.

[Ajanssen1993]

Ali, apologies - I've changed it :-)

Rorya, Thanks for looking into it! My data is sorted in order of dates. But somehow I get an error (#ERROR!) back. Could you have any idea what the reason is behind this error?

[romperstomper]

Perhaps you need a semicolon instead of a comma in the formula?

[Ajanssen1993]

Thanks Rorya, you nailed it! Cheers

[romperstomper]

Glad we could help.