Rank data with specific text

[sharky12345]

I'm using this formula to rank my data;

=RANK(B6,$B$6:$B$13)

Can anyone show me how to use specific text instead of a numeric value?

What I'd like is LOW, MEDIUM and HIGH instead if that's possible?

 

[Yongle]

How about

=IF(RANK(B6,$B$6:$B$13)/COUNTA($B$6:$B$13)>0.8,"low",IF(RANK(B6,$B$6:$B$13)/COUNTA($B$6:$B$13)>0.2,"medium","high"))


Amend .8 and .2 to whatever makes sense with the type of data you are analyzing

 

[sharky12345]

Perfect, thanks!

 

[Peter_SSs]

You haven't said exactly how the low/medium/high should be determined & if you really are only ranking 8 values that is quite a small sample.
Here is another much shorter option to consider, but note that it does not always produce identical results to the formula suggested by Yongle. Comes back to how you define low/medium/high especially with a small sample.

Excel Workbook
	B	C	D
6	5	medium	medium
7	6	medium	medium
8	8	high	high
9	2	low	low
10	1	low	low
11	4	medium	medium
12	6	medium	medium
13	7	high	medium
Rank

.. and wondering what you would want returned here?

Excel Workbook
	B	C	D
6	1000	high	high
7	500	medium	medium
8	500	medium	medium
9	500	medium	medium
10	500	medium	medium
11	1	low	medium
12	1	low	medium
13	1	low	medium
Rank (2)

 

[sharky12345]

Peter,

Thanks - I now understand that the answer is perhaps not as straightforward as you would think.

This one seems to do the trick;

=LOOKUP(PERCENTRANK($B$6:$B$13,B6),{0,0.2,0.8},{"LOW","MEDIUM","HIGH"})

 

[Peter_SSs]

Peter,

Thanks - I now understand that the answer is perhaps not as straightforward as you would think.

This one seems to do the trick;

You're welcome.