Calc spreadsheet file used in video: Right-click to download file
MIN, MAX, DMIN, and DMAX give us the largest or smallest value in a list, but what if we want the 2nd smallest or 3rd largest? or a list of the top 5. This is where you would use the LARGE and SMALL functions. They both have 2 parameters. First is the range of values to search. The second parameter is the rank (1 for 1st, 2 for 2nd and so on...).
In the following list of sales we want to get the 2nd highest sale, so this is where you would use the LARGE function:
To get the 3rd Lowest sales amount, the steps are the same, but we use the SMALL FUNCTION:
Now suppose I want the top 3 sales, we need a way to have increments of numbers which can be copied down relatively. For this I'll introduce 2 new functions: ROWS and COLUMNS, they count how many rows or columns are in an array of values (an array is a list). They both take 1 parameter, an array. I'll demonstrate the ROWS function in this example:
And there we have the number of rows we selected. COLUMNS works exactly the same way, but with columns. You can use the ROWS or COLUMNS to increment numbers inside of formulas:
You get a 1, it's returning the count of 1 row (D6). Click D6 again and pull the fill handle down a few rows, you'll see the numbers increment.
Double click the 3rd row down the formula reads =ROWS($A$1:A3), so it's returning 3 because it's counting 3 rows. The $A$1 is an absolute reference so it can't move down the rows, but the second reference is relative, so it's increasing by 1 each row which causes the ROWS function to indicate an increasing number of rows each row down. It doesn't matter which cell you use for the ROWS function since it's only counting the number of rows, it's not reading anything in the cells.
Press Esc to get out of edit mode.
This trick can be used with the LARGE or SMALL function to get a list of the top 3 sales from the example at the beginning of this lesson:
