+ Reply to Thread
Results 1 to 1 of 1

Question on How to return Maximum value out of a Conditional Statement

  1. #1
    Registered User
    Join Date
    Atlanta, United States
    MS-Off Ver
    Excel 2007

    Question on How to return Maximum value out of a Conditional Statement

    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.

    Last edited by czou6; 03-13-2013 at 03:48 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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