How to rank numbers with st, nd, rd, th.

August 12, 2018 at 04:07:08
Specs: Windows 7
I have created a rank related solution would you have a look and give me feedback. This is my first post. I thought a lot for long time to make it. :

=IFERROR(RANK(B1,$B$1:$B$25,1)&" "&
IF(OR(AND(MOD(RANK(B1,$B$1:$B$25,1),100)>=10,
(MOD(RANK(B1,$B$1:$B$25,1),100)<=14)),
MOD(RANK(B1,$B$1:$B$25,1),10)=0),"th",
CHOOSE(MOD(RANK(B1,$B$1:$B$25,1),10),
"st","nd","rd","th","th","th","th","th","th","th")),"")


See More: How to rank numbers with st, nd, rd, th.

Reply ↓  Report •

#1
August 12, 2018 at 07:47:39
Works as advertised, very nice.

Only improvement I can think of would be to use a Dynamic Named range in place of your 25 cell limit.

Here is a quick tutorial on how to create one, it uses the OFFSET() function:

https://www.excel-easy.com/examples...

Any problems or questions let us know.

MIKE

http://www.skeptic.com/


Reply ↓  Report •
Related Solutions


Ask Question