Author: Oscar Cronquist Article last updated on February 01, 2019

The formula in cell D3 is an array formula, it will extract the k-th smallest value ignoring zeros.

=SMALL(IF($B$3:$B$10=0, "", $B$3:$B$10), ROWS($A$1:A1))

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

IF($B$3:$B$10=0, "", $B$3:$B$10)

The logical expression $B$3:$B$10=0 compares the values in B3:B10 to 0 (zero) and returns a boolean value TRUE or FALSE in that location in the array.

IF({FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE},"",{1; 0; 7; 4; 0; 10; 8; 2})

If the value is 0 (zero) the IF function returns a blank "" in that postition in the array, if not 0 (zero) the IF function returns the number.

SMALL({1; ""; 7; 4; ""; 10; 8; 2}, ROWS($A$1:A1))

The ROWS function makes this formula dynamic, in cell D3 the ROWS function returns 1. Copy the cell to next cell below and it changes to ROWS($A$1:A2) and returns 2 making it return the second smallest value in the array in cell D4.

SMALL({1; ""; 7; 4; ""; 10; 8; 2}, 1)

and returns 1 in cell D3.

Regular formula

If you want to avoid an array formula then try this formula in cell D3:

=AGGREGATE(15,6,$B$3:$B$10/($B$3:$B$10<>0),ROWS($A$1:A1))

Download Excel file


 * You will also get a weekly newsletter, unsubscribe anytime!

Related articles