As in the case of the previously discussed function SUMPRODUCT (SUMPRODUCT), this function, at first glance, seems primitive and banal. But first impressions are deceiving, trust me 🙂
The main purpose of the function SELECTION is to extract the element we need from the set by its number (index). The basic syntax for this function is simple:
=SELECT(element_number; Element1; Element2 …)
where
- element_number – the ordinal number of the element we need (starting from 1)
- Elements 1 and 2… – list of elements (maximum 254)
For example, if we have a list of cities and we want to get the Nth of them, then we can type something like:
Of course, something similar can be done in other ways. For example, if the list of elements was not written directly in the function, but contained in the cells of the sheet, then it would be easier and more correct to use the function INDEX (INDEX), which we have already analyzed:
However, in some cases, it turns out to be more convenient to use just SELECTION, not alternatives – sometimes it’s easier to write an array of values uXNUMXbuXNUMXbin a formula immediately and rigidly than to try to calculate its elements on the fly or store it somewhere on a sheet. Let’s look at a couple of examples to illustrate.
So, for example, you can implement the formula to determine the number of working days in the current month (the values are taken from the production calendar for 2017 as an example):
In a very similar way, you can determine the number of the quarter of a regular and fiscal year (starts in July) for a given date:
“Not bad, but not wow” – you will say and you will be right. Further – more interesting.
Working with Ranges and Functions
In fact, not only numbers or text, but also cells and even entire ranges can act as list items. And this already opens up space for more serious designs. Let’s say we need to sum the quarter data by its number:
Function SELECTION, in this case, outputs a reference to the range-column, and the function SUM then adds up all its cells. In a sense, it turns out an alternative to the functions INDIRECT (INDIRECT) и DISPOSAL (OFFSET)which could also help in this situation.
nested functions
Further more. As a list of elements, there can be not only ranges, but functions. For example, you can choose to calculate the sum, mean, and median for a given range, switching between these functions on the fly:
A compact replacement for the classical condition checking function IF (IF), which here would also have to be invested in each other twice.
Bonding Ranges
If the set contains ranges, and the number of the element to be retrieved is not just a number, but an array of constants in curly braces, then the function SELECTION will produce an array at the output, which is a gluing of the corresponding ranges. Those. in the example above about quarters, you can calculate the amount for the first and third quarters with one formula:
In practice, it can be convenient to use such a trick to virtually, right in the formula, and not on the sheet, rearrange the columns and implement, for example, the “left VLOOKUP” trick, without changing the table itself in any way:
- Why you need the SUMPRODUCT function
- How to extract values to the left of the search column (left VLOOKUP)