Multiple Date Ranges Averae

Hi All - Hoping someone can help

I have a large amount of date formula's and I am trying to get the average journey step delay between 1 - 2, 2 - 3, 3 - 4 and so on.

I have the below to be used for the equation;

Timestamp and journey point (please see attached)

I have been playing around with IF formula, average if and min max, but i haven't managed a clear workout. What i have attached is on one example of a journey, but i have manage journeys included in my sheet, over 100. is anyone able to provide insight?

Thanks!

Capture.PNG

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

For those keen excel users I have attached a sample of the data for you to play around with.

Essentially I need the 1st journey step combine and a day duration spat out, then the difference between all the 2 and the 3rd journey time durations etc in order to understand the average duration within each step.

Thanks, let me know if you need to know anything else.

Looking at your data : first two rows (4,5) have 8 day difference, rows 5,6 have nearly a month difference: row 8 timestamp is later than row 9.

And how many touchpoint combinations are there.

Very confusing (to me).

Hey Glenn, that is exactly what I mean - I have been trying a few different ways but have looked at it for so long that I am muddle now.

I was looking at averageifs, but ideally i want to get it to a day format, from those calculations I don't seem to be able to get it to work through that.

I was hoping to have an equation to look at each of it's own unique journey ID's and to work out the day difference between those before averaging all the (for example) unique journeys 1 + 2 day calculations workings, but I have not managed to think of how to do that.

Does that make sense? Sorry it was vague, I have been looking at it for a while now.

Hey John,

Tell me about it - i have been staring at it for days - it would be a good way for me to display data, but I am not sure if it will get into the format I need it in.

So essentially all the unique journeys will have there own touchpoints and date delay between each of those, and then I would like to average it against all the other journeys and there 1 - 12 journeys in order to find the average it takes for users to get to the desired action.

I am just not sure I can work with all the different unique journeys.