User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Rank & Sort Numbers in a Column, Ignoring Error Values

 

Related Links: 

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

 


 

Rank & Sort Column Numbers, Skipping Error Values

 

 

Column A contains numbers, and also error values (#N/A, #DIV/0!, etc), in random order.

 

Column B: Ranks column A values, Ignoring Error values, in Descending Order (high to low). Enter Array formula (Ctrl+Shift+Enter) in cell B2 and copy down;

 

Column C: Sorts column A values, Ignoring Error values, in Descending Order (high to low), using the LARGE function. Enter Array formula (Ctrl+ Shift+ Enter) in cell C2 and copy down;

 

Column D: Sorts column A values, Ignoring Error values, in Descending Order (high to low), using the AGGREGATE function. Enter formula (Non-array) in cell D2 and copy down;

 

Column E: Sorts column A values, Ignoring Error values, in Ascending Order (high to low), using the SMALL function. Enter Array formula (Ctrl+ Shift+ Enter) in cell E2 and copy down;

 

Column F: Sorts column A values, Ignoring Error values, in Ascending Order (high to low), using the AGGREGATE function. Enter formula (Non-array) in cell F2 and copy down;

 

 

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

 

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

 

 

Rank / Sort Values Ignoring Errors

 

"Open Image in New Tab" for a full and clear view.

 

 

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.

   

 

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). 

 

 

 

Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.