Hi,
I am trying to rank my data in Excel based on two columns. The 2 columns are CYCLE and DURATION. The ranking should work like this:
It should rank CYCLE first then DURATION second.
The formula should work even if the data is not sorted or with blanks.
For instance, here is my sample data and the EXPECTED ranking:
Duration Cycle Rank 0:44:59 1 6 1:24:02 2 2 0:56:59 1 8 2:00:09 2 5 0:45:06 1 7 1:46:07 2 4 2:25:12 3 1 1:32:56 2 3
I found through a lengthy search an exact same scenario with mine, using this formula:
=COUNTIF($G$2:$G$9,">"&G2)+SUMPRODUCT(--($G$2:$G$9=G2),--($F$2:$F$9<F2))+1
It works ONLY if the data has no blank rows. My actual data has random blank or incomplete rows so the formula above will not work in my case.
I cannot attach the file here, the paper clip icon is not working. I uploaded the file through Manage Attachments and here is the link of my attachment:
http://www.excelforum.com/attachment...1&d=1459470449
If the link above does not work, here is another one:
http://filesxpress.com/download-74e4...4109d85337337d
Thank you and I hope you can assist.
Bookmarks