Formula to Identify Largest to Smallest Adjacent Cells

[chrismdusa]

Hi everyone. Thanks in advance for any help. I'm trying to identify/label cells in a range as largest to smallest, by labeling the largest in the range as "1" (placing the "1" in the adjacent cell), the next largest as "2", and so on. I do not want to rearrange the numbers being compared...no sorting. I only want to identify the largest through smallest in the adjacent cells.

In other words, in a worksheet, in cells B1 through B6, I have 421.34 in cell B1, 492.86 in cell B2, 344.59 in cell B3, 507.86 in cell B4, 546.55 in cell B5, and 171.73 in cell B6. These numbers are the result of formulas and may change from time to time. I want use formulas in cells C1 through C6 to automatically have a 1 through 6 appear in each cell in the adjacent range to identify the largest through the smallest numbers in B1 through B6. So in this worksheet, 1 should appear in C5, 2 should appear in C4, 3 in C2, 4 in C1, 5 in C3, and 6 in C6, but all automatically by formulas.

Does anyone have an idea of how this can be done with regular formuls...no macros or VBA? Thanks very much!

 

[maabadi]

Formula different Based Excel Version.
Try Use this:

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

And drag it down.

 

[chrismdusa]

Awesome, thanks! ......rank! I knew there was a word I was looking for when I typed up the post. It was "rank", lol. Thanks!

 

[maabadi]

You're Welcome.