With the increasing use of Excel, people are looking for different ways to automate their tasks and data assessments. The functions available offer a wide range of possibilities, being possible to apply them in different situations. In this sense, we will demonstrate how to build a ranking using the functions "major" and "minor", presenting in detail their structures.
Knowing the functions "greater" and "minor"
The functions we use in our example have very similar structures and easy assimilation, differentiating only by the fact that one will return the highest value and another the lowest value. Given the similarity, we will show the "larger" function whose syntax is as follows:
MAJOR (matrix k)
Both syntax arguments are required. The array corresponds to the data range from which the desired values are extracted. Already ok (k-th value) refers to the position of the highest value in the supplied array.
Understanding the concept of k-th value
K-th value is a term of Applied Statistics, which corresponds to the magnitude of a value in a given series. In a set formed by the numbers (4, 8, 7, 6, 2), for example, we can observe that the third greater value is "6". In this case, the kth value would be "3", equivalent to third. The kth of "8", the highest value of the set, would be "1", and so on. The same principle applies to the kth of the smallest value: for "4", in the example above, the kth would be "2" (second smallest value).
Building a ranking with the functions "major" and "minor"
To apply the functions in the construction of a ranking, we will first make the table, which will be our matrix. In our example, we will use a table that lists the total sales of the employees of a company in a certain period, sorting them in alphabetical order.
After this step, we will now prepare the cells in which the functions will be applied and displayed the ranking. For this, we will make a new table that will present the five largest and the five lowest total sales of employees. So we will be ready to apply the functions.
In the table that we create for the ranking, we will click on the cell that will display the highest sales total, which in our example will be the cell "H6", inserting the "greater" function. The matrix will be the table with the sales totals (if there were, in addition to the cells that display the sales figures, any other cell with numbers, we would have to be careful to only select the data range that would make the composition of the ranking - in this example , the interval between "B2" and "B15"). Completing the function, the "k" would be the 1 number, since we want to obtain the highest sales value. By typing "ENTER", we can see that the value of R $ 9.000,00 has actually been displayed.
A practical way to populate the other fields is, after locking the array, copy the function. First, we'll click the bottom corner of the cell, dragging it to the cells below. In the sequence, we will change the value of "k" according to the position in the ranking (in the cell "H3" the "k" would be the number 2 - second largest value, and so on).
When clicking on the cell "J6", we will insert the "minor" function, which has the same parameters as we know. Therefore, just repeat the previous steps so that we have in the ranking the five lowest total sales.
So, our ranking is finished, just do the formatting at will. Excel functions stand out for versatility and can be used for a variety of purposes, as we have seen in the example.
Please tell us below how you use the "major" and "minor" functions in your worksheets and keep track of our blog for more tips.