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