This is a very interesting topic if we are working in the food area, to find out the ranking of the best restaurants or entertainment. In which we need to create a top or trend table that displays the first places in music, movies, fashion, etc. Excel has good resources that helps us create a ranking with more than one criterion In a simple way. But first let’s be clear about certain terms.
It refers to a classification that establishes an order in the elements following one or more evaluation criteria. So that it places in the first place, the one that it considers a higher value than the second value and so on.
In Excel when creating a ranking, we recommend using dynamic tables if you have a version 2013 or higher. It’s easier and more manageable to work with, even more so if you understand a lot of data.
It would also be nice if you consider an array formula. It fits very well if you are using a interactive board or dynamic data (When the data changes frequently, such as once a week or once a month.)
Create a ranking in Excel with more than one criteria
Someone said, “with illustrations we understand each other better”. Well, we will explain you based on an example, so that you can understand how to use the formulas. We have a table with 4 columns. Column A contains list of people. Column B is the number (#) of operations. Column C the amount of total sales. In Column D we will place the ranking or the positions.
- Our objective will be to find the best sellers, according to their sales. In the Positions Column we will write the formula to meet our goal.
- We will use the Hierarchy function. Concentrating on the first criterion. The Sales of Column C. It should be noted that the data starts from cell C2 through cell C11.
- We will write: HIERARCHY.EQV (C2, C2: C11, 0). We put “0” to give us the right order.
- It is necessary to add absolute references to it so that it gives us the correct ranking. The definitive formula looks like this: HIERARCHY.EQV (C2, $ C $ 2: $ C $ 11; 0).
- Then we copy the formula of the entire Column D. And the ranking of the best sellers will begin to appear.
- For the tiebreaker add to the formula and it looks like this: HIERARCHY.EQV (C2, $ C $ 2: $ C $ 11; 0) + COUNTIF ($ C $ 2: C2; C2) = 1.
There is another way to tie the tie taking into account the 2nd criterion. The number of operations in column B. Comparing with this data we will achieve the tiebreaker. This is another alternative formula that encompasses the 2 criteria at once: = COUNTIF ($ C $ 2: $ C $ 11, “>” & C1) + 1 + SUMPRODUCT (- ($ C $ 1: $ C $ 99 = C1) , – ($ B $ 1: $ B $ 99> B1)).
We previously included the Hierarchy function. It consists of returning us the position that a value occupies, within a certain range of values. Its reference is: HIERARCHY (number; reference; order).
- The Number refers to the value or the position that we want to know within a group of elements.
- The reference would be the matrix where we will find the Number value.
- Order. It is a number of (0 or 1). Determine how we will order the values, based on the reference. So if you enter 0, it will search the position in descending order. If you enter 1, it will search the position in ascending order.
If you want to add more criteria, add more columns before the column you designate as ranking or positions. And match the formulas as many columns as you have included. And try to adjust the references to your data.
We hope that you can use all this knowledge for your new projects. It really is very interesting and fun when you see the results creating ranking in Excel with various criteria. Little by little you will discover the potential of Excel formulas. And you can tell us your opinions about it.