How to ignore zeros using the SMALL function
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.
Regular formula
If you want to avoid an array formula then try this formula in cell D3:
The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.
SMALL function with duplicates
The formulas in column E, shown in the picture above, extracts the k-th smallest value from B3:B9 ignoring the duplicate numbers. […]
The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […]
SMALL function – multiple conditions
The array formula in D10 extracts numbers sorted from small to large from column D if Region is equal to […]
SMALL function with multiple cell ranges
Today I learned how to sort numbers from multiple cell ranges thanks to Sam Miller. It is surprisingly simple and easy. […]
How to ignore error values using the SMALL function
The image above shows you a formula in cell D3 that tries to get the smallest number from cell range […]
The array formula in column E, shown in above picture sorts text values from column B. The Length columns prove […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.