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

T

tallshortguy

New Member
Joined
Mar 13, 2013
Messages
7
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
tallshortguy said:
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.
Click to expand...

Hello and welcome to mrExdel.
Could you post your actual data?
 
Upvote 0
Okay here is a sample worksheet.

Sheet1

*ABCDEF
1NameJob Start TimeJob End Timeinterval startinterval endActive
2q117/02/2012 07:00:0217/02/2012 07:00:0617/02/2012 07:00:0017/02/2012 07:00:010
3q217/02/2012 07:00:0217/02/2012 07:00:0817/02/2012 07:00:0117/02/2012 07:00:020
4q317/02/2012 07:00:0217/02/2012 07:00:0617/02/2012 07:00:0217/02/2012 07:00:033
5q417/02/2012 07:00:0317/02/2012 07:00:0517/02/2012 07:00:0317/02/2012 07:00:044
6q517/02/2012 07:00:5117/02/2012 07:01:2417/02/2012 07:00:0417/02/2012 07:00:054
7q617/02/2012 07:00:0517/02/2012 07:00:0617/02/2012 07:00:0517/02/2012 07:00:062
8q717/02/2012 07:00:1117/02/2012 07:00:2317/02/2012 07:00:0617/02/2012 07:00:071
9q817/02/2012 07:00:0617/02/2012 07:00:0817/02/2012 07:00:0717/02/2012 07:00:082
10q917/02/2012 07:00:0717/02/2012 07:00:0817/02/2012 07:00:0817/02/2012 07:00:090
11q1017/02/2012 07:00:0717/02/2012 07:00:0917/02/2012 07:00:0917/02/2012 07:00:103
12q1117/02/2012 07:00:0817/02/2012 07:00:1217/02/2012 07:00:1017/02/2012 07:00:112
13q1217/02/2012 07:00:0917/02/2012 07:00:1117/02/2012 07:00:1117/02/2012 07:00:122
14q1317/02/2012 07:00:0817/02/2012 07:00:0917/02/2012 07:00:1217/02/2012 07:00:131
15q1417/02/2012 07:20:1017/02/2012 07:20:1417/02/2012 07:00:1317/02/2012 07:00:141
16q1517/02/2012 07:00:0917/02/2012 07:00:1117/02/2012 07:00:1417/02/2012 07:00:151
17q1617/02/2012 07:00:0917/02/2012 07:00:1117/02/2012 07:00:1517/02/2012 07:00:161
18q1717/02/2012 07:04:5017/02/2012 07:16:3017/02/2012 07:00:1617/02/2012 07:00:171
19q1817/02/2012 07:20:1417/02/2012 07:20:4517/02/2012 07:00:1717/02/2012 07:00:181

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

Spreadsheet Formulas
CellFormula
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
tallshortguy said:
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.
Click to expand...

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.
 
Upvote 0
Robert Mika said:
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.
Click to expand...

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

Sheet1

*ABCDEFG
1NameJob Start TimeJob End Time*interval startinterval endActive
2q117/02/2012 07:00:0217/02/2012 07:00:06*17/02/2012 07:00:0017/02/2012 07:00:010
3q217/02/2012 07:00:0217/02/2012 07:00:08*17/02/2012 07:00:0117/02/2012 07:00:020
4q317/02/2012 07:00:0217/02/2012 07:00:06*17/02/2012 07:00:0217/02/2012 07:00:033
5q417/02/2012 07:00:0317/02/2012 07:00:05*17/02/2012 07:00:0317/02/2012 07:00:044
6q517/02/2012 07:00:5117/02/2012 07:01:24*17/02/2012 07:00:0417/02/2012 07:00:054
7q617/02/2012 07:00:0517/02/2012 07:00:06*17/02/2012 07:00:0517/02/2012 07:00:062
8q717/02/2012 07:00:1117/02/2012 07:00:23*17/02/2012 07:00:0617/02/2012 07:00:071
9q817/02/2012 07:00:0617/02/2012 07:00:08*17/02/2012 07:00:0717/02/2012 07:00:082
10q917/02/2012 07:00:0717/02/2012 07:00:08*17/02/2012 07:00:0817/02/2012 07:00:090
11q1017/02/2012 07:00:0717/02/2012 07:00:09*17/02/2012 07:00:0917/02/2012 07:00:103
12q1117/02/2012 07:00:0817/02/2012 07:00:12*17/02/2012 07:00:1017/02/2012 07:00:112
13q1217/02/2012 07:00:0917/02/2012 07:00:11*17/02/2012 07:00:1117/02/2012 07:00:122
14q1317/02/2012 07:00:0817/02/2012 07:00:09*17/02/2012 07:00:1217/02/2012 07:00:131
15q1417/02/2012 07:20:1017/02/2012 07:20:14*17/02/2012 07:00:1317/02/2012 07:00:141
16q1517/02/2012 07:00:0917/02/2012 07:00:11*17/02/2012 07:00:1417/02/2012 07:00:151
17q1617/02/2012 07:00:0917/02/2012 07:00:11*17/02/2012 07:00:1517/02/2012 07:00:161
18q1717/02/2012 07:04:5017/02/2012 07:16:30*17/02/2012 07:00:1617/02/2012 07:00:171
19q1817/02/2012 07:20:1417/02/2012 07:20:45*17/02/2012 07:00:1717/02/2012 07:00:181
20****17/02/2012 07:00:1817/02/2012 07:00:191
21****17/02/2012 07:00:1917/02/2012 07:00:201
22****17/02/2012 07:00:2017/02/2012 07:00:211

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

Spreadsheet Formulas
CellFormula
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:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
VBA to autosave and close after a set amount of time
Replies
0
Views
41
lojanica
L
H
  • Question
Equation or work around to find independent detections/occurrences from camera trap photos
Replies
5
Views
326
Joe4
Joe4
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
303
Fluff
Fluff
AOB
  • Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
248
AOB
AOB
T
  • Question
Optimising my VBA to improve the efficiency of my Workbook
2 3
Replies
21
Views
1K
Tmini
T

Forum statistics

Threads
1,214,516
Messages
6,119,979
Members
448,934
Latest member
audette89

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top