Descending ranking of highest values that are not always unique numbers

**Giake** · 02-23-2016, 05:56 PM

Hello Excel Experts,

I search formulas for descending ranking of highest values that are not always unique numbers.

I have three columns
Column A = increasing fixed numbers
Column B = variable not unique numbers(can change all the times)
Column C = need formulas to find the largest number in column B and to fill in that fixed number from column A. Than search the next second largest number and so on….

In the attachments :
I have input manually in column C the values I will like to have but is this possible with formulas?
In the file you will see also column E that looks interesting but maybe to complicate?

Thank to read my problem!

**dflak** · 02-24-2016, 04:56 PM

I don't see the pattern.

The 400 in cell B2 is number one because it is the biggest and first. The 400 in cell B9 is 2 because it is tied for number one, but comes after the first number one.

By this logic, the 50 in cell B3 is 8 because it is the 8th largest number.

But why is the 200 in cell B4 larger than the 350s in cells B5, B8 and B11?

I think the order should be 1, 8, 7, 3, 10, 9, 4, 2, 6, 5 respectively.

In the attached I look at two things: how many times the number appears before: Column D =COUNTIF($B$2:B2,B2) This formula takes advantage of Excel's absolute and relative cell referencing to make a sliding range for comparison.

The second thing I look at is how many numbers are bigger than it? Column E =COUNTIF($B$2:$B$11,">"&B2)

Then I combine the two: Column F = D + E +1. The biggest number is in zeroth place, so I add a one.

Column G is the same formula expressed in terms of Column B only. You can eliminate the helper columns. I left them there to show the logic.
=COUNTIF($B$2:B2,B2)+COUNTIF($B$2:$B$11,">"&B2)