Finding k-th largest time with number of rows differing from actual counts of time

[hongjie8811]

Hi I'm running a queue simulation and I need to find the k-th largest time. Typically I would use a Large formula, however, in this case, there is a count for each time. Meaning, the number of rows is not actually the counts of time. For example, in the case below, theres actually 13 counts of time with some time appearing more than once (e.g 10:56 appearing twice), while there is only 8 rows. Is there anyway I could still find the k-th largest time in this situation? Thanks in advance!

Count Time
1 10:40
1 10:45
2 10:56
1 10:32
3 10:18
2 10:50
1 10:58
2 11:13

[MrShorty]

Short but useless answer -- yes there is some way to find the k-th largest time. Here's how I would probably do it:

1) Sort in descending order by time.
2) Enter 0 in C2. In C3, enter a formula like =SUM(B$2:B2,0.5) -- note the mix of relative and absolute references. This builds a "lookup column" that we can use in an approximate match lookup function.
3) Then use a lookup function to find the value =INDEX(B2:B9,MATCH(k,C2:C9,1))

I'm sure there are other ways to do it, but that should at least show that it is possible.

[Bo_Ry]

Please enter K no. In C1
C2
=INDEX(B2:B99,LARGE(INDEX((ROW(A2:A99)-ROW(A1))*(A2:A99>=COLUMN(A1:C1)),),C1))

[hongjie8811]

Thanks for your suggestion! However, there are certain restrictions in my model which makes me unable to sort the time.

[hongjie8811]

Hi!
There seem to be missing arguments for the inner index which I can't figure out. Can you explain to me the idea behind this formula? Thanks!

[Bo_Ry]

It's not easy to explain,

=(ROW(A2:A9)-ROW(A1))*(A2:A9>=COLUMN(A1:C1))

A. (ROW(A2:A9)-ROW(A1)) = {1;2;3;4;5;6;7;8} row number start from 1,2,…

B. (A2:A9>=COLUMN(A1:C1))*1 = ({1;1;2;1;3;2;1;2} >= {1,2,3})*1

Value in A2:A9 compare to 1,2,3 from Column(A1:C1) if number in A2:A9 is more than 3, column(A1:Z1) give {1,2,…,26} this support max 26 count
1 >= 1,2,3 = True, False, False
2 >= 1,2,3 = True, True , False
3 >= 1,2,3 = True, True , True
True * 1 = 1
False* 1 = 0

(A2:A9>=COLUMN(A1:C1))*1 = ({1;1;2;1;3;2;1;2} >= {1,2,3})*1 = {1,0,0;1,0,0;1,1,0;1,0,0;1,1,1;1,1,0;1,0,0;1,1,0}

C. = A*B = (ROW(A2:A9)-ROW(A1))*(A2:A9>=COLUMN(A1:C1)) === {1,0,0;2,0,0;3,3,0;4,0,0;5,5,5;6,6,0;7,0,0;8,8,0}

=LARGE(C),1) = 8
=LARGE(C),2) = 8
=LARGE(C),3) = 7
=LARGE(C),4) = 6 …

Large of C will return row number for use with index(B2:B9,LARGE(C),1))

[hongjie8811]

Very nice! Thanks Bo_Ry, you have been a great help!! Can only imagine the amount of time you took to type all that out...

[Bo_Ry]

Happy to help and Thank you for the feedback.

[hongjie8811]

Hi sorry Bo_Ry, i realised the formula do not take into account the the time itself, but only the order of the time.
My above table is not sorted in ascending order, thus a smaller time might be below a larger time in rows.
The formula only counts upwards from the bottom row, which is not what I'm looking for.
Is there any way to fix it?

[Bo_Ry]

Please upload your sheet with expected result.

[MrShorty]

However, there are certain restrictions in my model which makes me unable to sort the time.

Can you explain these restrictions? As your latest question shows, somewhere in the algorithm needs to be a sort step. So, to say that your model makes it impossible to sort, then we need to understand why your model restricts you from sorting. With 1 million rows and 16 thousand columns, I see no reason why you could not move the sort step into a (hidden?) helper column/row/range. Or figure out some other strategy to sort the times. One way or another, it seems to me that your overall algorithm must somehow be aware of the sort order of those times.