Max

[Excelwig]

Hi

I need help with this scenario.

I have a table full of data and I need a the highest value from a range where the times matches the specified time slot. So I have say 12 different figures all for the 9am time slot.

for example

A B
time delay
9:00 100
9:00 200
9:00 50
9:00 299
9:00 499
9:00 609
10:00 100
10:00 243
10:00 435
9:00 355
9:00 444
11:00 743
11:00 123
9:00 543
9:00 512

So I'm not sure where to go I have sumif working fine to add the columns where the time matches what is specified.

So I have a list of time slots on another sheet and need this to look up where say b2 (value being 9:00) matches any entries on sheet2 and return the max delay time for that time slot.

There is about 1000 rows in total and the 9:00 slot appears in diff places down it so I can't just specify a range

 

[Rick Rothstein]

Sure: this is why I wrote about sorting the data first. Otherwise it does not make any sense.

Sorry, I skimmed your response too quickly and missed the "sort the data" part of it. However, I note some much better solutions than either of ours in the last few posts.

 

[Excelwig]

Thanks everyone for your help the Aggregate solution worked the best!

 

[cr731]

Two options...


[TABLE="width: 288"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: white"]time
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]delay
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl64, width: 64, bgcolor: white"]9:00
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]time
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]9:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]100
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]609
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9:00
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]9:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]200
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]609
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]9:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]50
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]9:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]299
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]9:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]499
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]9:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]609
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]10:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]100
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]10:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]243
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]10:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]435
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]9:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]355
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]9:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]444
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]743
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]11:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]123
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]9:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]543
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: white"]9:00
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]512
[/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl63, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

[1]

D2, just enter:

=AGGREGATE(16,6,B2:B16/(A2:A16=D1),1)

[2]

F3, just enter:

=DMAX(A1:B16,2,F1:F2)

Can the aggregate method be adapted to allow two criteria (i.e. A2:A16=D1 and B2:B16<>E1)?

 

[Aladin Akyurek]

Can the aggregate method be adapted to allow two criteria (i.e. A2:A16=D1 and B2:B16<>E1)?

Yes, you can do that:

AGGREGATE(14,6,B2:B16/((A2:A16=D1)*(B2:B16<>E1)),1)