## How to ignore zeros using the SMALL function

*Article updated on December 18, 2017*

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

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.

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

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.

and returns 1 in cell D3.

### Download Excel *.xlsx

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article