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