User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Rank & Sort Numbers in a Column, Ignoring Error Values

 

 

Rank & Sort Column Numbers, Skipping Error Values 

 

Column B: Ranks column A values, Ignoring Error values, in Descending Order (high to low). Column A contains numbers, and also error values (#N/A, #DIV/0!, etc), in random order. Enter Array formula (Ctrl+Shift+Enter) in cell B2 and copy down;

  

-----------------------------------------------------------------------------------------------

 

Column B Ranks column A values, Ignoring Error values, in Descending Order (high to low). 

 

Rank / Sort Values Ignoring Errors  

 

Cell B2 formula:  

 

=IF(ISNUMBER(A2),SUM(--IF( ISNUMBER( A$2:A$20), (A2< A$2:A$20))) +1,"")

 

Explanation - Cell B2 formula (Array formula):

 

1) IF(ISNUMBER(A$2:A$20), (A2<A$2:A$20)): returns an array of TRUE & FALSE values - {FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE;...} - where TRUE indicates where cell A2 value is less than the other column A values. ISNUMBER function is used to refer to only those column A values which are numbers thereby ignoring error values.

 

2) Using double negation converts TRUE to 1 and FALSE to zero, and using SUM returns the number of column A values less than cell A2 value and adding 1 returns A2's rank in the column.

 

 

-----------------------------------------------------------------------------------------------

 

Related Formulas - illustrated in the Ebook of Excel Formulas.

   

 

Column C: Cell C2 formula uses the LARGE function with ISNUMBER and is entered as an Array formula (Ctrl+Shift+Enter). It Sorts column A values, Ignoring Error values, in Descending Order (high to low).

 

Column D: Cell D2 formula uses the AGGREGATE function and is entered as a Non-Array formula. It Sorts column A values, Ignoring Error values, in Descending Order (high to low).

 

Column E: Cell E2 formula uses the SMALL function with ISNUMBER and is entered as an Array formula (Ctrl+Shift+Enter). It Sorts column A values, Ignoring Error values, in Ascending Order (low to high).

 

Column F: Cell F2 formula uses the AGGREGATE function and is entered as a Non-Array formula. It Sorts column A values, Ignoring Error values, in Ascending Order (low to high).