0

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
0

Excel has built in functions to calculate the rank of a number within a set of numbers.

  • 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.

enter image description here

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.