CODEWITHSUNDEEP
excel
Git84
Git84

Reputation: 75

Excel Rank Multiple Columns

I'm facing a issue with ranking in Excel particularly in regards to tie breaking. I tried several options but i guess they don't fit my issue. Its quite simple really, I'll explain:

The Data:

1   2   3   4   5   6   7   8   9   10


87  83  74  95  69  90  73  0   74  85
121 121 96  121 121 121 121 83  121 121

As you can see its easy for me to rank the first line (I'm working in columns instead of rows for the data). When i do a Rank Function gives the following result:

3   5   6   1   9   2   8   10  6   4

Which is correct. The problem arises in the second line. There are ties because all of them reach the maximum of 121:

1   1   9   1   1   1   1   10  1   1

What i would like to do is take the first row as a tie breaker. So even if there is a tie the first line which was firstly text but now is a sequence from 1 to 10 could provide as secondary criteria to order the rank, thus giving the following ranking line:

1   2   9   3   4   5   6   10  7   9

Could one achieve this result?

Thank You very much in advance.

Upvotes: 0

Views: 1470

Answers (1)

eshwar
eshwar

Reputation: 694

You need a helper row to break the tie. You can add a fraction of the first row to the second row to create a new row & use the new row to rank

enter image description here

A4 = A3+(A2/(MAX($A$2:$J$2)+1))

Using the MAX I ensure the fraction is less than 1 which is adequate to break ties in this case.

A6 = RANK(A4,$A$4:$J$4)

You can hide the helper row if you dont want to show it.

Upvotes: 1

Related Questions