Rank - Circular Error

[How_Do_I]

How would I go about sorting those times (highest at the top, shortest at the bottom) please? Everything I've tried is giving me a circular error. The only thing is, the times are in an expandable range that increases as and when I add data. I'm happy to have help columns to solve this.

Excel Workbook
	B	H
18	5	00:52:09
19	12	00:44:44
20	19	00:49:32
21	26	01:04:28
22	33	01:23:09
23	40	00:51:37
24	47	01:27:29
25	54	01:16:52
26	63	01:00:26
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[Gerald Higgins]

Not sure but in B18, can you change the bits that say

ROWS($B$18:B18)

to something else, such as

ROWS($C$18:C18)

?

 

[How_Do_I]

Hello can anyone see what is going wrong here please? My C18 downwards is working. However, I need the row numbers and that is supposed to be my B18 downwards. However when I Index the row numbers in D18 downwards you can see that my returns are not the same in C18 and D18 downwards.

Sheet1

	A	B	C	D
		Wednesday

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]

[TD="align: right"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]63[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]01:27:29[/TD]
[TD="align: right"]01:00:26[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]54[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]01:23:09[/TD]
[TD="align: right"]01:16:52[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]47[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]01:16:52[/TD]
[TD="align: right"]01:27:29[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C17 =Q4 D17 =SUMPRODUCT(--(Day=C17),--(Distance>0.1)) A18 =C18=D18 B18 {=IF(E18="","",LARGE(IF(Day=$C$17,IF(Distance>0.1,ROW(Duraton)-ROW(INDEX(Duraton,1))+1)),E18))} C18 {=IF(E18="","",LARGE(IF(Day=$C$17,IF(Distance>0.1,Duraton,E18)),E18))} D18 =INDEX(Duraton,B18) <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

Names in Formulas Cell Name Applies to D17 Day =Sheet1!$N$16:INDEX(Sheet1!$N$16:$N$1512,Sheet1!$O$4) D17 Distance =Sheet1!$P$16:INDEX(Sheet1!$P$16:$P$1512,Sheet1!$O$4) B18 Day =Sheet1!$N$16:INDEX(Sheet1!$N$16:$N$1512,Sheet1!$O$4) B18 Distance =Sheet1!$P$16:INDEX(Sheet1!$P$16:$P$1512,Sheet1!$O$4) B18 Duraton =Sheet1!$Q$16:INDEX(Sheet1!$Q$16:$Q$1512,Sheet1!$O$4) C18 Day =Sheet1!$N$16:INDEX(Sheet1!$N$16:$N$1512,Sheet1!$O$4) C18 Distance =Sheet1!$P$16:INDEX(Sheet1!$P$16:$P$1512,Sheet1!$O$4) C18 Duraton =Sheet1!$Q$16:INDEX(Sheet1!$Q$16:$Q$1512,Sheet1!$O$4) D18 Duraton =Sheet1!$Q$16:INDEX(Sheet1!$Q$16:$Q$1512,Sheet1!$O$4) <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[How_Do_I]

I see what is going wrong with B18 now, it's returning the largest row number that meets the criteria as opposed to the largest duration which is what I need.

 

[How_Do_I]

=IF(F18="","",LARGE(IF(Day=$D$17,IF(Distance>0.1,ROW(Duraton)-ROW(INDEX(Duraton,1))+1)),F18)) entered with CSE given the highest row number, what I want is the row number of the highest duration. Any ideas please?

 

[How_Do_I]

Solution found, thanks everyone.

https://www.youtube.com/watch?v=rCeTG2k2T6I