MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Ranking


Posted by Catalina on October 06, 2001 10:48 AM

I want to RANK a list, but my Ref value is a list of VLOOKUPS which sometimes returns a "#N/A." How can I tell my RANK to ignore or skip cells with "#N/A"?


Posted by Aladin Akyurek on October 06, 2001 12:06 PM

Catalina,

Lets the values that VLOOKUPs return to be in A from A1 on.

In B1 enter: =IF(ISNUMBER(A1),A1,"") [ copy down as far as needed ]

In C1 enter: =IF(ISNUMBER(RANK(B1,$B$1:$B$5,1)),RANK(B1,$B$1:$B$5,1),"") [ copy down as far as needed ]

Aladin

==========