# Ranking two columns with different categories

I am looking at how to rank two columns separately and then take the average of the two ranks. One column is a percentage and one is a just a number.

Column A is the name of the org, column B is a number of item, column C is a percentage and column D would be the rank.

• Can you please edit your question to give us sample data, expected outcome, what you've tried so far and how it fails to meet expectations? – cybernetic.nomad Jan 10 at 15:11
• Could you provide a sample or screenshot about your problem? – Lee Jan 11 at 1:22

• `RANK.AVG` returns the rank of a number. If there are several equal numbers, it returns the average rank. So if the numbers are {1, 2, 2, 3}, the number 2 is both the second and third ranked number, the average rank is 2.5.
• `Rank.EQ` also returns the rank of a number. If there are several equal numbers, it returns the rank of the highest. So if the numbers are {1, 2, 2, 3}, the number 2 is the second highest number, so its rank is 2.
• There is also a `RANK` function, but this is only included for compatibility with Excel 2007 and earlier. Microsoft recommends using one of the first two functions instead.
As you want to take the average of two different ranks anyway, I think `RANK.AVG` is the most appropriate. Assuming your data is in the range A1:C5 with row 1 being the headers, you can put the following formula in cell D2 and copy it to the other cells in that column.
``````=AVERAGE(RANK.AVG(B2,B\$2:B\$5,0),RANK.AVG(C2,C\$2:C\$5,0))
The formula takes the average of the rank of the number in column B and the percentage in column C. Note that the third argument for the `RANK.AVG` function indicates if the numbers should be ranked in descending or ascending order (0 is descending, 1 is ascending). The formula I gave ranks in descending order; if you want to rank in ascending order, change the third argument of both `RANK.AVG` functions from `0` to `1`.