User Rating: 5 / 5

Rank & Sort Numbers in a Column, Ignoring Error Values

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

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