Find Median Time of Data Given Time Parameters

  • ,Hi,


    I'm working with data where I've been given a table of dates and times corresponding to different events. What I am trying to do now is group those times together to find both the percentage of events that occur during specific time range and then to also find the median event time for that range. For example, I need to find both the number of events occurring between 12:00AM - 5:59AM, 6:00AM to 11:59AM, 12:00PM - 5:59PM and 5:59PM - 11:59 PM. Finding the number of events was easy, I just used a simple COUNTIFS statement to count all times between the bounds of each bin. As for the median time calculation, I'm getting stuck. I tried using the following formula to no avail: MEDIAN(IF(AND(Table1[Time]<=X2,Table[Time]),Table1[Time])) where Table1[Time] represents the entire set of time data in Table1 and X2 and Y2 represent the first bin of time range (12:00AM - 5:59PM) but it kept throwing back what appears to be the median of the entire data set. I was thinking I'm better off trying to come up with a macro that will loop through each of the data points and using and if statement, add them to the correct array to then take the median of. Any insight and help on structure/syntax is greatly appreciated.

  • Hi,


    Your formula looks fine ...


    Could you attach a small sample file ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Forgot to remind you of the fact that, with the conditions you are adding, you are dealing with an Array Formula ...


    i.e. instead of the Enter key, you do need to use simultaneously the 3 keys : Control Shit Enter


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • As far as the generic structure of your Array Formula is concerned ...


    You can adapt following formula to your specific situation


    Code
    1. =MEDIAN(IF((yourRange>=LowerLimit)*(yourRange<=UpperLimit),yourRange))

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Quote

    Any insight and help on structure/syntax is greatly appreciated.


    Any feedback is greatly appreciated ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks Carim! I'm not sure why my formula wasn't working even though I was pressing CTRL + SHIFT + ENTER after constructing the formula. Nevertheless, I tried your formula and for some reason excel liked your way better. Now I actually get what I'm trying to find. Thanks for the help!

  • Thanks for :


    1. The FeedBack :)

    2. Your Thanks ;)

    AND

    3. the Like

    :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)