Hi, I have this worksheet which has jobs coming in during a day with their arrival time, service start time, and service end time. For every second of that day it finds how many jobs are running during that time (active column in the 2.17 Active tab). I'm trying to figure out a way to find for every job what the maximum number of jobs running along with it is. So, I figured it would involve a formula to find which time intervals overlap with the job's service time interval and finds the largest corresponding active number among those intervals. For example, if I had a job who's service time interval overlaps with 3 time intervals, and their corresponding active values are 3, 4, 6, then the formula would return the largest of those, 6. I've attached an example worksheet.
testjobs solution.xlsx
=IF(AND(NOT(job service start>=interval end time), NOT(job service end<=interval service start)),TRUE, FALSE)
I have the above which returns true if the job overlaps with any of the intervals. I've also played around with the max, vlookup, and lookup functions but I'm stuck. Is there some sort of way to put those in a vector and lookup which one has the largest corresponding active value? If there's another way please let me know. Any help is appreciated.
Thanks.
Bookmarks