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

Get the Excel file


SMALL-function-ignore-0.xlsx

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles