User Rating: 5 / 5     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).

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