Rank in Excel Based on Condition
Rank Excel data and cope with tie data using Excel formula. While designing the Excel Dashboards on the site, I realised I needed to rank information which was more complex than using Excel's built in Rank function. I needed to rank based on conditions and I needed this rank to be unique. Which meant having a ranking of 1 given to different regions or departments and not duplicating a ranking if there was a tie. The following Excel formula will Rank based on 1 condition.
Condition to Rank
Lets say we have a region in column A - Europe. In column C the numbers we are ranking on appear. Our task will be to test to see if firstly the region is equal to Europe and second where the numbers rank in comparison. If the data set looked like the following table of data.
The following formula counts how many times the number on the row is greater than the initial figure. In the table above 6,812,151 is compared in the list of numbers and the count is 1, one time there is a number greater than this figure in row 4. The formula is as follows.
=COUNTIFS($A$2:$A$11,$A2,$C$2:$C$11,">"&C2)
The result is 1 but we need this to say 2 so we add the count of that specific number.
COUNTIFS($A$2:$A2,$A2,C$2:C2,C2)
Bringing both formula together we just add the two.
=COUNTIFS($A$2:$A$11,$A2,$C$2:$C$11, ">" & C2) + COUNTIFS($A$2:$A2,$A2,C$2:C2, C2)
The result is the correct answer for all of the numbers in the list.
Here is some example raw data where there are multiple conditions which need to be ranked in order of the condition. For example, The First Region is Europe, Europe contains some countries which need to be ranked in order of the figures associated with them. The Excel formula ranks all of the regions in accordance to their ranking within the region. The Excel file attached show the workings of the above example.
