March 1, 2012 Leave a comment
The SMALL function returns the nth-smallest value within a range. The smallest value is ranked number 1.
The Syntax of the function is
data: A collection of values. data may be a number,date,duration,set is a collection containing number, date, or duration values. All values must be of the same type.
rank_c: A number representing the size ranking of the value you want to retrieve. ranking is a number value and must be in the range of 1 to the number of values in the collection.
Notes:A ranking of 1 retrieves the smallest number in the collection, 2 the second-smallest, and so on. Values included in the collection that are of the same size are ranked together, but impact the outcome.
We will try to understand the function with an example.
The sample data set is follows:
if we want to identify the small three numbers of above data set conventional way is as follows:
The formula in cell E3 is
=SMALL($B$2:$B$12,D3) and drag it down till E5 we will get the 1st smallest 2nd smallest and 3rd smallest.
other way to extract largest 3 value with more robust formula is with use of ROWS function. the formula will be
=SMALL($B$2:$B$11,ROWS(E$3:E3)) and drag it down here we do not need a helped column D where argument rank_c.
Google Docs way of writing the formula is
In the above formula you do not require to drag any thing the formula will automatically put 1st, 2nd and 3rd smallest value in cell E3 to E5.
if we want to largest numbers across the columns
The formula in cell I3 is
=SMALL($B$2:$B$11,COLUMNS($I3:I3)) just drag it across.
if we want the sum of large three numbers in single cell, formula will be as follows:
which is nothing but sum of 113+126+154 = 393.
With the use to small function we can extract data of duplicate records as shown in the above figure.
the formula in cell E15 is
the formula in cell F15 is
=COUNTIF($B$2:$B$12,E15) and drag it down
and the formula in cell G15 is
=IF(COLUMNS($G15:G15)>$F15,””,INDEX($A$2:$A$12,SMALL(IF($B$2:$B$12=$E15,ROW($B$2:$B$12)-ROW($B$2)+1),COLUMNS($G15:G15)))) drag the formula down and across.
the formula in cell G15 can be modified with filter formula in google docs way of writing
=transpose(FILTER(A:A,B:B=E15)) and drag it down.