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

 

[VoG]

Try

Excel Workbook
	A	B	C	D
1	time	delay		09:00
2	09:00	100		609
3	09:00	200
4	09:00	50
5	09:00	299
6	09:00	499
7	09:00	609
8	10:00	100
9	10:00	243
10	10:00	435
11	09:00	355
12	09:00	444
13	11:00	743
14	11:00	123
15	09:00	543
16	09:00	512
Sheet5

 

[Rick Rothstein]

You should be able to use this array-entered** formula (adjust the ranges as needed)....

=MAX(IF(A1:A16=TIME(9,0,0),B1:B16))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Excelwig]

Thanks I'll try those

just a thought when I used arrays last time It was slow to calculated and the spreadsheet slowed down. Is there a away without using an array ?

 

[J.Ty.]

Hi,

Sort your data by time (first level) and delay (second level), both increasing. If you cannot sort your data, make a sorted copy elsewhere.
Then you have the follwing two formulas.

Excel 2010
	A	B	C	D
1	time	delay		09:00
2	09:00	50		609
3	09:00	100		609
4	09:00	200
5	09:00	299
6	09:00	355
7	09:00	444
8	09:00	499
9	09:00	512
10	09:00	543
11	09:00	609
12	10:00	100
13	10:00	243
14	10:00	435
15	11:00	123
16	11:00	743
Sheet1

Cell Formulas
Range	Formula
D2	=INDEX($B$2:$B$16,MATCH(D1,$A$2:$A$16,1))
D3	=IF(INDEX($A$2:$A$16,MATCH(D1,$A$2:$A$16,1))<>$D$1,"No such time slot",INDEX($B$2:$B$16,MATCH(D1,$A$2:$A$16,1)))

The one in D2 is simpler, but has a disadvantage: if you enter a time slot which does not exist (e.g. by making a typo), you get an accidental value.
The one in D3 is more complicated, but has an advantage: if you enter a time slot which does not exist, you get an error message.

J.Ty.

 

[Weazel]

I'm not sure what, if any, the speed increase would be but similiar to the max if but wouldn't require control shift enter

=MAX(INDEX((A2:A16=D1)*B2:B16,0))

 

[Rick Rothstein]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=INDEX($B$2:$B$16,MATCH(D1,$A$2:$A$16,1))
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=IF(INDEX($A$2:$A$16,MATCH(D1,$A$2:$A$16,1))<>$D$1,"No such time slot",INDEX($B$2:$B$16,MATCH(D1,$A$2:$A$16,1)))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Those formulas appear to work only when the last time slot for the specified time contains the maximum value. For the data the OP posted, change (say) cell B4 777 and then try your formulas.

 

[J.Ty.]

Those formulas appear to work only when the last time slot for the specified time contains the maximum value. For the data the OP posted, change (say) cell B4 777 and then try your formulas.

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

J.Ty.

 

[Aladin Akyurek]

Thanks I'll try those

just a thought when I used arrays last time It was slow to calculated and the spreadsheet slowed down. Is there a away without using an array ?

Two options...


[TABLE="width: 288"]
<COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><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)

 

[Aladin Akyurek]

I'm not sure what, if any, the speed increase would be but similiar to the max if but wouldn't require control shift enter

=MAX(INDEX((A2:A16=D1)*B2:B16,0))

No, it shouldn't. In fact, we have here an additional function call. But see post #9 for faster options.