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.
Formula in cell H3:
The SMALL function ignores text and blank cells, however, not error values.
Explaining formula in cell H3
Step 1 - Enable multiple cell ranges
The first argument in the SMALL function is the array parameter: SMALL(array, k).
Use parentheses to enable multiple cell ranges in the first argument.
($B$3:$B$8, $D$3:$D$6, $F$3:$F$7)
The , (comma) separates the cell references.
Step 2 - Extract k-th smallest number
The second argument allows you to specify which number to extract based on their sort order.
ROWS($A$1:A1)
The ROWS function allows you to insert new rows and columns in your worksheet without breaking the formula.
The cell reference contains two parts, one is an absolute cell reference and the other is a relative cell reference.
The $ sign allows you to specify an absolute cell reference, this cell reference does not change when you copy the formula to cells below.
Get Excel *.xlsx file
SMALL function with multiple cell ranges.xlsx
Small category
This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]
This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]
The image above shows you a formula in cell D3 that tries to get the smallest number from cell range […]
Excel categories
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.
