CODEWITHSUNDEEP
excelfunction
Makai
Makai

Reputation: 617

Excel Finding What Place a Value Comes In

I am trying to find a way to get which place a certain value comes in in a range of other values. Or another way to look at it is I want to turn the SMALL function inside out. The SMALL function looks like this, "=SMALL(array, k)" where it looks for the k place in an array. I need the find what the k is. I hope that makes sense, but if not here is one more example.

        A         B                C            D
1   Kate        12.25   =PLACE($B$1:$B$13,B1)    4
2   Cindy       15.80   =PLACE($B$1:$B$13,B2)   10
3   Mark        11.85   =PLACE($B$1:$B$13,B3)    3
4   Thomas      12.98   =PLACE($B$1:$B$13,B4)    5
5   George      13.58   =PLACE($B$1:$B$13,B5)    7
6   Kim         14.52   =PLACE($B$1:$B$13,B6)    9
7   Tim         11.54   =PLACE($B$1:$B$13,B7)    2
8   Frank       12.99   =PLACE($B$1:$B$13,B8)    6
9   Fran        17.85   =PLACE($B$1:$B$13,B9)   11
10  Caroline    14.25   =PLACE($B$1:$B$13,B10)   8
11  Alex        19.20   =PLACE($B$1:$B$13,B11)  12
12  Lilly       25.20   =PLACE($B$1:$B$13,B12)  13
13  Peter       11.22   =PLACE($B$1:$B$13,B13)   1

Also I can easily do it with VBA, but I do not want to. I want to do it in cell and I do not want to have to sort them first.

Upvotes: 0

Views: 55

Answers (1)

barry houdini
barry houdini

Reputation: 46371

RANK function does this, i.e. in your case this formula in row 1 copied down

=RANK(B1,B$1:B$13,1)

The 1 at the end indicates the order of ranking (ascending here). Leave that out and you get descending ranking

Upvotes: 1

Related Questions