# How do I find the Max value out of condition with multple matches

#### tallshortguy

##### New Member
I'm new here so I'm not sure if there's a way I can attach a sample worksheet, which may make it easier to see what I'm having trouble with. Basically I have a list of jobs for a certain day along with the time the job began and the time it ended. Within that day I've made time intervals for every second of that day starting at 7AM and have a formula that finds the number of jobs whose job start to job end time interval overlaps with the time interval I created (this I've called Active). This way I know how many jobs are running concurrently throughout the day. I would like to create a new column that finds for every job what the largest number of jobs is that ran concurrently. So I need to find which time intervals the job overlaps with and find the largest Active values that correspond with those intervals.

For example, say job 1 occurred between 7:00 AM (job start time) and 7:03 AM (job end time). It overlapped with 3 intervals, 7:00 (interval start time)-7:01 (interval end time) AM with an Active of 9, 7:01-7:02 AM with an Active of 12, and 7:02-7:03 AM with an Active of 4. I need a formula that returns the max Active for job 1, which in this example would be 12.

Columns
D: Job start time
E: Jobs end time
F: Interval start time (for example, 7:00 AM)
G: Interval end time (for example, 7:01 AM)
H: Active

I know the logic/condition for finding whether the job's time interval overlaps with a certain interval in the day (overlaps if neither (Job Start >= Interval end) nor (Job End <= Interval start)).

Any help would be greatly appreciated.

Last edited:

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Robert Mika

##### MrExcel MVP
I'm new here so I'm not sure if there's a way I can attach a sample worksheet, which may make it easier to see what I'm having trouble with. Basically I have a list of jobs for a certain day along with the time the job began and the time it ended. Within that day I've made time intervals for every second of that day starting at 7AM and have a formula that finds the number of jobs whose job start to job end time interval overlaps with the time interval I created (this I've called Active). This way I know how many jobs are running concurrently throughout the day. I would like to create a new column that finds for every job what the largest number of jobs is that ran concurrently. So I need to find which time intervals the job overlaps with and find the largest Active values that correspond with those intervals.

For example, say job 1 occurred between 7:00 AM (job start time) and 7:03 AM (job end time). It overlapped with 3 intervals, 7:00 (interval start time)-7:01 (interval end time) AM with an Active of 9, 7:01-7:02 AM with an Active of 12, and 7:02-7:03 AM with an Active of 4. I need a formula that returns the max Active for job 1, which in this example would be 12.

Columns
D: Job start time
E: Jobs end time
F: Interval start time (for example, 7:00 AM)
G: Interval end time (for example, 7:01 AM)
H: Active

I know the logic/condition for finding whether the job's time interval overlaps with a certain interval in the day (overlaps if neither (Job Start >= Interval end) nor (Job End <= Interval start)).

Any help would be greatly appreciated.

Hello and welcome to mrExdel.
Could you post your actual data?

#### tallshortguy

##### New Member
Hello and welcome to mrExdel.
Could you post your actual data?

Yes, what method should I use to post my data? The FAQ said no attachments. Thanks.

#### Robert Mika

##### MrExcel MVP
Yes, what method should I use to post my data? The FAQ said no attachments. Thanks.

#### tallshortguy

##### New Member
Okay here is a sample worksheet.

Sheet1

 * A B C D E F 1 Name Job Start Time Job End Time interval start interval end Active 2 q1 17/02/2012 07:00:02 17/02/2012 07:00:06 17/02/2012 07:00:00 17/02/2012 07:00:01 0 3 q2 17/02/2012 07:00:02 17/02/2012 07:00:08 17/02/2012 07:00:01 17/02/2012 07:00:02 0 4 q3 17/02/2012 07:00:02 17/02/2012 07:00:06 17/02/2012 07:00:02 17/02/2012 07:00:03 3 5 q4 17/02/2012 07:00:03 17/02/2012 07:00:05 17/02/2012 07:00:03 17/02/2012 07:00:04 4 6 q5 17/02/2012 07:00:51 17/02/2012 07:01:24 17/02/2012 07:00:04 17/02/2012 07:00:05 4 7 q6 17/02/2012 07:00:05 17/02/2012 07:00:06 17/02/2012 07:00:05 17/02/2012 07:00:06 2 8 q7 17/02/2012 07:00:11 17/02/2012 07:00:23 17/02/2012 07:00:06 17/02/2012 07:00:07 1 9 q8 17/02/2012 07:00:06 17/02/2012 07:00:08 17/02/2012 07:00:07 17/02/2012 07:00:08 2 10 q9 17/02/2012 07:00:07 17/02/2012 07:00:08 17/02/2012 07:00:08 17/02/2012 07:00:09 0 11 q10 17/02/2012 07:00:07 17/02/2012 07:00:09 17/02/2012 07:00:09 17/02/2012 07:00:10 3 12 q11 17/02/2012 07:00:08 17/02/2012 07:00:12 17/02/2012 07:00:10 17/02/2012 07:00:11 2 13 q12 17/02/2012 07:00:09 17/02/2012 07:00:11 17/02/2012 07:00:11 17/02/2012 07:00:12 2 14 q13 17/02/2012 07:00:08 17/02/2012 07:00:09 17/02/2012 07:00:12 17/02/2012 07:00:13 1 15 q14 17/02/2012 07:20:10 17/02/2012 07:20:14 17/02/2012 07:00:13 17/02/2012 07:00:14 1 16 q15 17/02/2012 07:00:09 17/02/2012 07:00:11 17/02/2012 07:00:14 17/02/2012 07:00:15 1 17 q16 17/02/2012 07:00:09 17/02/2012 07:00:11 17/02/2012 07:00:15 17/02/2012 07:00:16 1 18 q17 17/02/2012 07:04:50 17/02/2012 07:16:30 17/02/2012 07:00:16 17/02/2012 07:00:17 1 19 q18 17/02/2012 07:20:14 17/02/2012 07:20:45 17/02/2012 07:00:17 17/02/2012 07:00:18 1

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:82px;"><col style="width:130px;"><col style="width:130px;"><col style="width:130px;"><col style="width:130px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

 Cell Formula D2 =INT(B2)+TIMEVALUE("07:00:00") E2 =D2+TIME(0,0,1) F2 =COUNTIFS(B:B,"<=" & D2,C:C,">="&E2) D3 =E2 E3 =D3+TIME(0,0,1) F3 =COUNTIFS(B:B,"<=" & D3,C:C,">="&E3) D4 =E3 E4 =D4+TIME(0,0,1) F4 =COUNTIFS(B:B,"<=" & D4,C:C,">="&E4) D5 =E4 E5 =D5+TIME(0,0,1) F5 =COUNTIFS(B:B,"<=" & D5,C:C,">="&E5) D6 =E5 E6 =D6+TIME(0,0,1) F6 =COUNTIFS(B:B,"<=" & D6,C:C,">="&E6) D7 =E6 E7 =D7+TIME(0,0,1) F7 =COUNTIFS(B:B,"<=" & D7,C:C,">="&E7) D8 =E7 E8 =D8+TIME(0,0,1) F8 =COUNTIFS(B:B,"<=" & D8,C:C,">="&E8) D9 =E8 E9 =D9+TIME(0,0,1) F9 =COUNTIFS(B:B,"<=" & D9,C:C,">="&E9) D10 =E9 E10 =D10+TIME(0,0,1) F10 =COUNTIFS(B:B,"<=" & D10,C:C,">="&E10) D11 =E10 E11 =D11+TIME(0,0,1) F11 =COUNTIFS(B:B,"<=" & D11,C:C,">="&E11) D12 =E11 E12 =D12+TIME(0,0,1) F12 =COUNTIFS(B:B,"<=" & D12,C:C,">="&E12) D13 =E12 E13 =D13+TIME(0,0,1) F13 =COUNTIFS(B:B,"<=" & D13,C:C,">="&E13) D14 =E13 E14 =D14+TIME(0,0,1) F14 =COUNTIFS(B:B,"<=" & D14,C:C,">="&E14) D15 =E14 E15 =D15+TIME(0,0,1) F15 =COUNTIFS(B:B,"<=" & D15,C:C,">="&E15) D16 =E15 E16 =D16+TIME(0,0,1) F16 =COUNTIFS(B:B,"<=" & D16,C:C,">="&E16) D17 =E16 E17 =D17+TIME(0,0,1) F17 =COUNTIFS(B:B,"<=" & D17,C:C,">="&E17) D18 =E17 E18 =D18+TIME(0,0,1) F18 =COUNTIFS(B:B,"<=" & D18,C:C,">="&E18) D19 =E18 E19 =D19+TIME(0,0,1) F19 =COUNTIFS(B:B,"<=" & D19,C:C,">="&E19)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

#### Robert Mika

##### MrExcel MVP
Looking at your data all names are unique- is this the Job number?
Besides you have two MAX(4) - do you need to return first,last of both?

#### tallshortguy

##### New Member
Sorry I probably should have made it more clear. The interval times and active columns don't correspond with the jobs they're in the same row with. Also yes each job is unique so I assigned a name for each.

#### Robert Mika

##### MrExcel MVP
Sorry I probably should have made it more clear. The interval times and active columns don't correspond with the jobs they're in the same row with. Also yes each job is unique so I assigned a name for each.

I'm confused.
If each job is unique the MAX will be the Activity cell itself.
If that not the case cpudl you give an example of desired result.

#### tallshortguy

##### New Member
I'm confused.
If each job is unique the MAX will be the Activity cell itself.
If that not the case cpudl you give an example of desired result.

I'm uploading this new worksheet so maybe it will become more clear.

Sheet1

 * A B C D E F G 1 Name Job Start Time Job End Time * interval start interval end Active 2 q1 17/02/2012 07:00:02 17/02/2012 07:00:06 * 17/02/2012 07:00:00 17/02/2012 07:00:01 0 3 q2 17/02/2012 07:00:02 17/02/2012 07:00:08 * 17/02/2012 07:00:01 17/02/2012 07:00:02 0 4 q3 17/02/2012 07:00:02 17/02/2012 07:00:06 * 17/02/2012 07:00:02 17/02/2012 07:00:03 3 5 q4 17/02/2012 07:00:03 17/02/2012 07:00:05 * 17/02/2012 07:00:03 17/02/2012 07:00:04 4 6 q5 17/02/2012 07:00:51 17/02/2012 07:01:24 * 17/02/2012 07:00:04 17/02/2012 07:00:05 4 7 q6 17/02/2012 07:00:05 17/02/2012 07:00:06 * 17/02/2012 07:00:05 17/02/2012 07:00:06 2 8 q7 17/02/2012 07:00:11 17/02/2012 07:00:23 * 17/02/2012 07:00:06 17/02/2012 07:00:07 1 9 q8 17/02/2012 07:00:06 17/02/2012 07:00:08 * 17/02/2012 07:00:07 17/02/2012 07:00:08 2 10 q9 17/02/2012 07:00:07 17/02/2012 07:00:08 * 17/02/2012 07:00:08 17/02/2012 07:00:09 0 11 q10 17/02/2012 07:00:07 17/02/2012 07:00:09 * 17/02/2012 07:00:09 17/02/2012 07:00:10 3 12 q11 17/02/2012 07:00:08 17/02/2012 07:00:12 * 17/02/2012 07:00:10 17/02/2012 07:00:11 2 13 q12 17/02/2012 07:00:09 17/02/2012 07:00:11 * 17/02/2012 07:00:11 17/02/2012 07:00:12 2 14 q13 17/02/2012 07:00:08 17/02/2012 07:00:09 * 17/02/2012 07:00:12 17/02/2012 07:00:13 1 15 q14 17/02/2012 07:20:10 17/02/2012 07:20:14 * 17/02/2012 07:00:13 17/02/2012 07:00:14 1 16 q15 17/02/2012 07:00:09 17/02/2012 07:00:11 * 17/02/2012 07:00:14 17/02/2012 07:00:15 1 17 q16 17/02/2012 07:00:09 17/02/2012 07:00:11 * 17/02/2012 07:00:15 17/02/2012 07:00:16 1 18 q17 17/02/2012 07:04:50 17/02/2012 07:16:30 * 17/02/2012 07:00:16 17/02/2012 07:00:17 1 19 q18 17/02/2012 07:20:14 17/02/2012 07:20:45 * 17/02/2012 07:00:17 17/02/2012 07:00:18 1 20 * * * * 17/02/2012 07:00:18 17/02/2012 07:00:19 1 21 * * * * 17/02/2012 07:00:19 17/02/2012 07:00:20 1 22 * * * * 17/02/2012 07:00:20 17/02/2012 07:00:21 1

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:82px;"><col style="width:130px;"><col style="width:130px;"><col style="width:130px;"><col style="width:130px;"><col style="width:130px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

 Cell Formula E2 =INT(B2)+TIMEVALUE("07:00:00") F2 =E2+TIME(0,0,1) G2 =COUNTIFS(B:B,"<=" & E2,C:C,">="&F2) E3 =F2 F3 =E3+TIME(0,0,1) G3 =COUNTIFS(B:B,"<=" & E3,C:C,">="&F3) E4 =F3 F4 =E4+TIME(0,0,1) G4 =COUNTIFS(B:B,"<=" & E4,C:C,">="&F4) E5 =F4 F5 =E5+TIME(0,0,1) G5 =COUNTIFS(B:B,"<=" & E5,C:C,">="&F5) E6 =F5 F6 =E6+TIME(0,0,1) G6 =COUNTIFS(B:B,"<=" & E6,C:C,">="&F6) E7 =F6 F7 =E7+TIME(0,0,1) G7 =COUNTIFS(B:B,"<=" & E7,C:C,">="&F7) E8 =F7 F8 =E8+TIME(0,0,1) G8 =COUNTIFS(B:B,"<=" & E8,C:C,">="&F8) E9 =F8 F9 =E9+TIME(0,0,1) G9 =COUNTIFS(B:B,"<=" & E9,C:C,">="&F9) E10 =F9 F10 =E10+TIME(0,0,1) G10 =COUNTIFS(B:B,"<=" & E10,C:C,">="&F10) E11 =F10 F11 =E11+TIME(0,0,1) G11 =COUNTIFS(B:B,"<=" & E11,C:C,">="&F11) E12 =F11 F12 =E12+TIME(0,0,1) G12 =COUNTIFS(B:B,"<=" & E12,C:C,">="&F12) E13 =F12 F13 =E13+TIME(0,0,1) G13 =COUNTIFS(B:B,"<=" & E13,C:C,">="&F13) E14 =F13 F14 =E14+TIME(0,0,1) G14 =COUNTIFS(B:B,"<=" & E14,C:C,">="&F14) E15 =F14 F15 =E15+TIME(0,0,1) G15 =COUNTIFS(B:B,"<=" & E15,C:C,">="&F15) E16 =F15 F16 =E16+TIME(0,0,1) G16 =COUNTIFS(B:B,"<=" & E16,C:C,">="&F16) E17 =F16 F17 =E17+TIME(0,0,1) G17 =COUNTIFS(B:B,"<=" & E17,C:C,">="&F17) E18 =F17 F18 =E18+TIME(0,0,1) G18 =COUNTIFS(B:B,"<=" & E18,C:C,">="&F18) E19 =F18 F19 =E19+TIME(0,0,1) G19 =COUNTIFS(B:B,"<=" & E19,C:C,">="&F19) E20 =F19 F20 =E20+TIME(0,0,1) G20 =COUNTIFS(B:B,"<=" & E20,C:C,">="&F20) E21 =F20 F21 =E21+TIME(0,0,1) G21 =COUNTIFS(B:B,"<=" & E21,C:C,">="&F21) E22 =F21 F22 =E22+TIME(0,0,1) G22 =COUNTIFS(B:B,"<=" & E22,C:C,">="&F22)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Basically each job, it's start and end time is it's own entity. The time interval and active cells were originally on another tab but I pasted it into this worksheet so that's probably why you thought it corresponded with the job in the same row. The active cell only corresponds with the interval start and end, not the unique job it's in the same row as. In this new worksheet you can see that there are more time intervals than jobs so they are no corresponding. So if you look at the time interval from 7:00:02-7:00:03 jobs q1, q2, and q3 times all fall into that interval so the active cell is 3.

Last edited:

#### Robert Mika

##### MrExcel MVP
What I asked for was deisred result which I still do not see...

Replies
7
Views
282
Replies
6
Views
133
Replies
32
Views
438
Replies
3
Views
253
Replies
3
Views
151

1,195,631
Messages
6,010,783
Members
441,569
Latest member
PeggyLee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back