Most likely you are familiar with simple functions MIN (MIN) и MAX (MAX)that allow you to quickly find the minimum or maximum value in the table. But what if we need to find, for example, not the largest, but the 2nd or 5th value in the TOP? Functions will help here LARGE (LARGE) и LEAST (SMALL).
The syntax for these functions is similar:
=LARGE(Range; Position)
=SMALL(Range; Position)
where
- Range – the range of cells with numbers that we are checking.
- Position – an integer representing the position (rank, number in the rating) of the item to be retrieved.
For example, if we have a table with profits by product, then using the function LARGE you can easily determine the maximum (first in the rating) value from the profit range (B2: B22), which will actually be identical to the MAX formula (B2: B22):
Similarly, the function
=LARGE(B2:B22)
… will give the next maximum (2nd in the rating), etc.
Array of constants and sum of TOPs
What’s interesting is the argument Position can be not just a number, but a set of numbers – an array of constants in curly braces. So, for example, to get the sum of the first three maximum values in a range, you can use a formula with an array of constants written inside for the first three positions (combination Ctrl + Shift + Enter at the end, you can not press, although in fact this is an array formula):
Sort by formula
Function LEAST (SMALL) It is very convenient to use for sorting a set of numerical values by a formula. To do this, it is enough to make an auxiliary numbering (1, 2, 3…) and refer to it in the second argument:
If instead of SMALL you use the LARGE function, then sorting, of course, will already be in descending order.
Numbers only
Another useful feature of these functions is that they ignore everything except numbers, i.e. “Do not see” the text and logical values (TRUE, FALSE). It can be useful to use, for example, to retrieve all amounts for a given name, when a product appears more than once and VPR (VLOOKUP) won’t help anymore:
In this example, the IF function in column C checks the name against the given name (Cucumbers) and outputs the sum or logical FALSE. And then to extract all the received amounts from column C, our function LOWEST is used, which ignores FALSE – and we get a list of the values of all transactions for the desired product.
- How to extract not the first, but all values at once (Multi VLOOKUP)
- How to sort lists (numeric, text) using array formulas
- Finding the minimum and maximum value by condition