I am in search of Rank Function with multiple criteria

[Kumar Shanmugam]

Rank Function is used to sort data in ascending and descending order. but it work only with one criteria. I searched in many forums but could not find any Rank Funcation using multiple criteria. Instead of that, they have used sumproduct which is difficult to understand. I am in prefer of Rank Function using multiple criteria. Please find attached the sample data.

 

[Hui]

I would use a helper Column H
H5: =C5+D5/200
Copy down
Then in I5: =RANK(H5,$H$5:$H$10,0) copy down
see attached file:

Enjoy

 

[Kumar Shanmugam]

Thanks for the prompt revert. It worked but I didn't understand the logic D5/200.why 200 not 100

 

[bosco_yip]

Thanks for the prompt revert. It worked but I didn't understand the logic D5/200.why 200 not 100

You can use ...C5+D5/200 or ...C5+D5/100,

Try this formula without helper column :

In E5, copy down :

=SUMPRODUCT(--((C$5:C$11+D$5:D$11/100)>(C5+D5/100)))

Or,

=SUMPRODUCT(--((C$5:C$11+D$5:D$11/200)>(C5+D5/200)))

Regards
Bosco

 

[Hui]

I choose a round number greater than the Maximum value of the 2nd field
If I had choosen 100, any value in field 2 > 100 would cause an extra 1 to be added to Field 1
so eg:

Field 1, Field 2
4, 150
5, 25

According to the current logic /200 record 2 is highest then record 1
But if you use /100,
Record 1 = 4+1.5 = 5.5
Record 2 = 5+0.25 = 5.25
Record 1 is highest

You need to use a divisor greater than the largest number, unless that is the outcome you desire

Thanks for the prompt revert. It worked but I didn't understand the logic D5/200.why 200 not 100

 

[NARAYANK991]

Hi Shanmugam ,

The simple logic is that since there are 3 digits involved in the scores for Avg. Prod. , dividing by 1000 will give the same result.

If there were 4 digits in one measure , you would use 10000 as the divisor.

This logic would always ensure that one measure forms the integer part , while the other measure forms the decimal part.

Narayan