The HIERARCHY.EQV function is usually used to solve statistical calculations; for example: a set of workers achieve certain percentages in their results and from them it is derived from 1st to 5th place; However, some workers were tied in these results, remaining with the same percentage; How do we make this a hierarchy in this case?
There are two ways to resolve the matter, On the one hand we will analyze how to use the HIERARCHY.EQV function, solving only its variables with the data we have from the table; and on the other, combining it with the COUNTIF function; which will allow us to give a different hierarchy, even if there are repeated values; pay close attention to get it right.
You can save yourself all this by learning to use pivot tables with dynamic values, whose tutorial we explain in our blog.
Rank only using the Hierarchy.eqv function with its basic variables
Let’s start by analyzing the parameters that the formula requests to work; being as follows: = HIERARCHY.EQV (number; reference; order); Each of these data to be solved must be kept in our table; and represent the following:
- Number: is the value we want to find out in range is.
- Reference: is the set of data with which we will compare the first data. To select them you just have to take the first data and drag until the last data.
- Order: Although Excel says that it is optional, we recommend that you enter 0; that is to say descending.
At this point some observations; the reference must be set as fixed, using the f4 button or Fn + F4 on some laptops. On the other hand, notice in the image that, effectively position 3 is repeated twice, jumping to 4th place and reaching 5th place.
You may also be interested in:
To prevent this from happening, we need to combine another function that will allow us to also tell about this place that we have skipped; the function we are talking about is: COUNT YES.
Combining the HIERARCHY.EQV and COUNTIF function;
In the previous result we saw that place number 3 was repeated, but with the count function, if we will solve it, take into account that we need the values that we now have repeated to take the place that they correspond to, what you should do is the following:
- We copy and paste the previous formula and add it with COUNTIF; being that way: = HIERARCHY.EQV (G4, $ G $ 4: $ G $ 8.0) + COUNTIF ($ G $ 4: G4, G4) -1.
- After this we can give enter and it will give us the result of the first row.
- If we want the result to be reflected in the other cells, we drag the cell from the lower right corner to the last data. It should fit as we show you in the image.
This is how simple it is to rank repeated data with the hierarchy.eqv function. nested to the count.si function. Now, notice that the result is messy; to fix it you need to know how to make an advanced and dynamic filter in excel; We discussed this topic recently on our blog and we are sure that it will be of great use to you, with which you can complement the results that we explain to you now.
We can link access data tables and take advantage of that data to rank, it is a very useful tool because it increases the range of data that we can have to generate information, if you do not know how to do it you can take advantage of this article.
On the other hand, there are situations in which more than one criterion will be necessary to make the ranking that we have discussed in this blog and you can review it. We are delighted to have helped you solve this problem of prioritizing your data, but now it is your turn, so it would not be bad for you to share with your colleagues this content and everything we have here to learn Excel. Tell us below how it went and if you could solve it easily.
You may also be interested in:
I'm sorry this content was not useful for you!
Let me improve this content!
Tell me, how can I improve this content?