+ Reply to Thread
Results 1 to 7 of 7

Multiple Date Ranges Averae

  1. #1
    Registered User
    Join Date
    06-17-2021
    Location
    Sydney
    MS-Off Ver
    10 Pro
    Posts
    7

    Post 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

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2312
    Posts
    13,432

    Re: Multiple Date Ranges Averae

    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.

  3. #3
    Registered User
    Join Date
    06-17-2021
    Location
    Sydney
    MS-Off Ver
    10 Pro
    Posts
    7

    Re: Multiple Date Ranges Average

    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.
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,535

    Re: Multiple Date Ranges Averae

    You provided no manually calculated results and your explanation is a bit vague.


    Do you want the average of the timepoint 2 minus time point 1s... followed by the average of timepoint 3 minus time point 2s? If not EXACTLY what do you want??
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,789

    Re: Multiple Date Ranges Average

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

  6. #6
    Registered User
    Join Date
    06-17-2021
    Location
    Sydney
    MS-Off Ver
    10 Pro
    Posts
    7

    Re: Multiple Date Ranges Averae

    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.

  7. #7
    Registered User
    Join Date
    06-17-2021
    Location
    Sydney
    MS-Off Ver
    10 Pro
    Posts
    7

    Re: Multiple Date Ranges Average

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] SUMIFS Help - Multiple date ranges
    By dspblues in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 10-18-2014, 07:38 AM
  2. Replies: 2
    Last Post: 08-16-2013, 10:18 AM
  3. pulling multiple date ranges from 1 cell into multiple rows?
    By 13lack13lade in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2013, 11:50 PM
  4. [SOLVED] Counting the number of times a date occurs in multiple start and end date ranges.
    By Grizz in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-05-2013, 04:41 PM
  5. Analyse date frequency cross-referencing multiple date ranges
    By globalpontoon in forum Excel General
    Replies: 2
    Last Post: 05-15-2012, 04:24 PM
  6. Replies: 8
    Last Post: 01-07-2011, 07:36 AM
  7. Count Multiple Criteria within Multiple Date Ranges
    By E6BAV8R in forum Excel General
    Replies: 3
    Last Post: 12-08-2010, 05:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1