Rank - Circular Error

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows
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
BH
18500:52:09
191200:44:44
201900:49:32
212601:04:28
223301:23:09
234000:51:37
244701:27:29
255401:16:52
266301:00:26
Sheet1
#VALUE!
</td></tr></table></td></tr></table>
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows
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

ABCD
17 Wednesday9
18FALSE6301:27:2901:00:26
19FALSE5401:23:0901:16:52
20FALSE4701:16:5201:27:29

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

Spreadsheet Formulas
CellFormula
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
CellNameApplies to
D17Day=Sheet1!$N$16:INDEX(Sheet1!$N$16:$N$1512,Sheet1!$O$4)
D17Distance=Sheet1!$P$16:INDEX(Sheet1!$P$16:$P$1512,Sheet1!$O$4)
B18Day=Sheet1!$N$16:INDEX(Sheet1!$N$16:$N$1512,Sheet1!$O$4)
B18Distance=Sheet1!$P$16:INDEX(Sheet1!$P$16:$P$1512,Sheet1!$O$4)
B18Duraton=Sheet1!$Q$16:INDEX(Sheet1!$Q$16:$Q$1512,Sheet1!$O$4)
C18Day=Sheet1!$N$16:INDEX(Sheet1!$N$16:$N$1512,Sheet1!$O$4)
C18Distance=Sheet1!$P$16:INDEX(Sheet1!$P$16:$P$1512,Sheet1!$O$4)
C18Duraton=Sheet1!$Q$16:INDEX(Sheet1!$Q$16:$Q$1512,Sheet1!$O$4)
D18Duraton=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
 
Last edited:

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Last edited:

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows
=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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,840
Messages
5,598,386
Members
414,234
Latest member
grlevesq

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
Top