Ranking - multiple criteria

[Lugashz]

Port Time Price Rank
M 1 10
M 2 11
M 3 12
M 1 13
S 1 10
S 2 9
S 3 8

Hi folks,
hope you will be able to help me. I need a ranking for an example above. The first key is the Time (the less the better) and second key is Price (the less the better). It has to match with Port range so range 1 would be Port M aso.

Many thanks!
Lukas

[jason.b75]

So we don't come up with many wrong answers, please enter the expected results into your sample.

[cbatrody]

Hi,

Try the following formula in D2:

=COUNTIF($B$2:$B$8,"<"&B2)+1+SUMPRODUCT(--($C$2:$C$8=C2),--($B$2:$B$8>B2))

See the attached file.

[Lugashz]

Port Time Price Rank
M 1 10 1
M 2 11 3
M 3 12 4
M 1 13 2
S 1 10 1
S 2 9 2
S 3 8 3

This is how it should look like the Time is more Important, in case the Time is equal for two or more cells, then the Price is decisive. Also as I mentioned before I need to consider the Port range.

[Lugashz]

thanks, but this didnt solve it :/

[jason.b75]

Try this one

Please Login or Register  to view this content.

This assumes that your sample table is in A1:D8 (results in column D)

[Lugashz]

Try this one

Please Login or Register  to view this content.

This assumes that your sample table is in A1:D8 (results in column D)

So in my example it works great, but in my real file it doesnt, can I attach somehow an xls file here?

[Lugashz]

Try this one

Please Login or Register  to view this content.

This assumes that your sample table is in A1:D8 (results in column D)

So in my example it works great, but in my real file it doesnt, can I attach somehow an xls file here?

[jason.b75]

So in my example it works great, but in my real file it doesnt, can I attach somehow an xls file here?

Click on 'Go Advanced' at the bottom right or the reply box, then look for the paperclip icon (top row, 3rd from right).

There is a limit on file size so you might have to copy a reasonable size sample to upload.

[Lugashz]

Example, try2 attached

[jason.b75]

That N/A entry makes a lot of difference.
Try this one.

Please Login or Register  to view this content.

[Lugashz]

That N/A entry makes a lot of difference.
Try this one.

Please Login or Register  to view this content.

Awesome, thanks!!!