Ranking closest to 100

[vlewis85]

I need to rank a bunch of cells - closest to 100 radiating outward both above and below 100. With the data below, A12 should be rank 1, A9 should be rank 2, A6 should be rank 3, etc. I am familiar with the RANK function but I can't figure out how to set the value of 100 as the baseline where rank 1 would start. Thanks for your help!

A1-A15
86.19
88.36
90.41
85.94
97.26
100.75
89.91
97.06
99.45
104.07
106.59
99.89
95.45
105.2
103.89

[alansidman]

Look at the attached. Does that work for you?

Alan

[vlewis85]

That is perfect! Thank you so much!

[vlewis85]

Follow up question:

What if I wanted to keep the same data, but I want the results to rank closest to 100 without going over. This would give me a 1 through 11 rank, and then take the cells that went over 100 and rank them 12-15. 12 being closest to 100 from the >100 side of the scale.

[alansidman]

A little bit of extra work for that. Here is an example.

Alan

[daddylonglegs]

You could use a single formula, i.e. in row 3 copied down

=IF(A3<=100,COUNTIFS(A$3:A$17,"<=100",A$3:A$17,">"&A3)+1,RANK(A3,A$3:A$17,1))

[vlewis85]

@daddylonglegs

That is amazing you got it into a single formula. I've been trying to read through it and learn how it was constructed.

Here is how I see it:

The IF fail is easy... If A3 isn't less than or equal to 100, rank within the range normally. This would give the cells that went over 100 the highest ranks as needed.

If the IF is true... Count within the range how many cells are less than or equal to 100 (that would give me 10), then count within the range (and this is where im stuck) how many cells are greater than ??? as well as A3? What exactly does A$3:A$17,">"&A3) mean?

Does the first count of 10 provide the formula with the range of rankings? (1-10)

[zbor]

COUNTIFS look BOTH criterias where:

A$3:A$17 is less or equal than 100

AND

A$3:A$17 greater then A3

[vlewis85]

That makes sense, thank you!