Ranking - Non unique Values : Small To Large

**Jaime1182** · 09-22-2012, 10:46 PM

I'm trying to rank data where more than one cell may contain the same value, but the ranking will skip a number.

I'm trying to get it to rank from small to large.

DaddyLongLegs posted a really good solution:
=SUM(--ISNUMBER(MATCH(ROW(INDIRECT("1:"&RANK(A1,A$1:A$6))),RANK(A$1:A$6,A$1:A$6),0)))
confirmed with CTRL+SHIFT+ENTER

(http://www.excelforum.com/excel-gene...-required.html)

but what it does is rank values from big to small. Is there a way to invert this formula to get it to go from small to big?

Thanks!

**FDibbins** · 09-23-2012, 01:53 AM

try changing the last 0 (...RANK(A$1:A$6,A$1:A$6),0)))...to 1

**Ace_XL** · 09-23-2012, 06:51 AM

You would need to rank ascending..

Hence use
=SUM(--ISNUMBER(MATCH(ROW(INDIRECT("1:"&RANK(A1,A$1:A$6,1))),RANK(A$1:A$6,A$1:A$6,1),0)))
Confirm with Ctrl+Shift+Enter

FDibbins' suggestion will change the MATCH function to a 'less than' MATCH and not an 'exact' match instead of inverting the rank order, hence will not work