I'm trying to count simultaneous occurrences in a time series to plot (possibly by another criteria)
a) simultaneous activities in 15 minute slot
b) busiest times of day
Context:
I have a production line running continuous 24/7. I receive an output (1 line of data) of setting changes when they occur. I am trying to count the number of things that change in a rolling 15 minute time slot. (i.e. how many other things are going on around the same time that action was carried out) There are >1m lines of data to scan through.
Data is organised:
Machine | "Dial" | Time | "activity"
in a formula I would write it as:
=COUNTIFS([Time],">"&[@Time]-15/(24*60),[Time],"<"&[@Time]+15/(24*60))
and
=COUNTIFS([Time],">"&[@Time]-15/(24*60),[Time],"<"&[@Time]+15/(24*60),[Activity],"ON")
I want the output to look like this:
Machine | "Dial" | Time | "activity" | "No of simultaneous" | "No of simultaneous activity"
I found this which looked close:
http://www.mrexcel.com/forum/excel-...lications]-count-number-occurances-array.html
but couldn't work out how to make it output to the rows (i.e. not the summarised solution but similar to the User's first attempt)
a) simultaneous activities in 15 minute slot
b) busiest times of day
Context:
I have a production line running continuous 24/7. I receive an output (1 line of data) of setting changes when they occur. I am trying to count the number of things that change in a rolling 15 minute time slot. (i.e. how many other things are going on around the same time that action was carried out) There are >1m lines of data to scan through.
Data is organised:
Machine | "Dial" | Time | "activity"
in a formula I would write it as:
=COUNTIFS([Time],">"&[@Time]-15/(24*60),[Time],"<"&[@Time]+15/(24*60))
and
=COUNTIFS([Time],">"&[@Time]-15/(24*60),[Time],"<"&[@Time]+15/(24*60),[Activity],"ON")
I want the output to look like this:
Machine | "Dial" | Time | "activity" | "No of simultaneous" | "No of simultaneous activity"
I found this which looked close:
http://www.mrexcel.com/forum/excel-...lications]-count-number-occurances-array.html
but couldn't work out how to make it output to the rows (i.e. not the summarised solution but similar to the User's first attempt)