by The tutorial explains the specificities of the Excel Rank functions and shows how to do ranking in Excel based on multiple criteria, rank data by group, calculate percentile rank, and more.
When you need to determine the relative position of a number in a list of numbers, the easiest way is to sort the numbers in ascending or descending order. If for some reason sorting is not possible, a Rank formula in Excel is the perfect tool to do the job.
Excel RANK function
The RANK function in Excel returns the order (or rank) of a numeric value compared to other values in the same list. In other words, it tells you which value is the highest, the second highest, etc.
In a sorted list, the rank of a certain number would be its position. The RANK function in Excel can determine the rank starting with the largest value (as if sorted in descending order) or the smallest value (as if sorted in ascending order).
The syntax of the Excel RANK function is as follows:
Where:
Number (required) - the value whose rank you'd like to find.
Ref (required) - a list of numeric values to rank against. It can be supplied as an array of numbers or a reference to the list of numbers.
Order (optional) - a number that specifies how to rank values:
- If 0 or omitted, the values are ranked in descending order, i.e. from largest to smallest.
- If 1 or any other non-zero value, the values are ranked in ascending order, i.e. from smallest to largest.
Excel RANK.EQ function
RANK.EQ is an improved version of the RANK function, introduced in Excel 2010. It has the same syntax as RANK and works with the same logic: if several values are ranked equally, the highest rank is assigned to all such values. (EQ stands for "equal").
In Excel 2007 and lower versions, you should always use the RANK function. In Excel 2010, Excel 2013, and Excel 2016, you can go with either RANK or RANK.EQ. However, it'd be wise to use RANK.EQ because RANK can be discontinued at any moment.
Excel RANK.AVG function
RANK.AVG is another function to find rank in Excel that is available only in Excel 2010, Excel 2013, Excel 2016 and later.
It has the same syntax as the other two functions:
The difference is that if more than one number has the same rank, the average rank is returned (AVG stands for "average").
4 things you should know about RANK in Excel
- Any Rank formula in Excel works only for numeric values: positive and negative numbers, zeros, date and time values. Non-numeric values in the ref argument are ignored.
- All RANK functions return the same rank for duplicate values, and skip subsequent ranking, as shown in the example below.
- In Excel 2010 and later versions, the RANK function has been replaced with RANK.EQ and RANK.AVG. For backward compatibility, RANK still works in all versions of Excel, but it may not be available in the future.
- If number is not found within ref, any Excel Rank function would return the #N/A error.
Basic Excel Rank formula (from highest to lowest)
To gain more understanding about ranking data in Excel, please have a look at this screenshot:
All three formulas rank numbers in column B in descending order (the order argument omitted):
In all versions of Excel 2003 - 2016:
=RANK($B2,$B$2:$B$7)
In Excel 2010 - 2016:
=RANK.EQ($B2,$B$2:$B$7)
=RANK.AVG($B2,$B$2:$B$7)
The difference is in how these formulas process duplicate values. As you see, the same score appears twice, in cells B5 and B6, which affects subsequent ranking:
- The RANK and RANK.EQ formulas give a rank of 2 to both duplicate scores. The next highest score (Daniela) is ranked 4th. A rank of 3 is not given to anyone.
- The RANK.AVG formula assigns a different rank to each duplicate behind the scenes (2 and 3 in this example), and returns the average of those ranks (2.5). Again, the 3rd rank is not assigned to anyone.
How to use RANK in Excel - formula examples
The path to excellence, they say, is paved with practice. So, to better learn how to use RANK function in Excel, alone or in combination with other functions, let's work out solutions to a few real-life tasks.
How to rank in Excel from lowest to highest
As shown in the above example, to rank numbers from highest to lowest, you use one of the Excel Rank formulas with the order argument set to 0 or omitted (default).
To have number ranked against other numbers sorted in ascending order, put 1 or any other non-zero value in the optional third argument.
For example, to rank the 100-meter sprint times of the students, you can use either of the below formulas:
=RANK(B2,$B$2:$B$7,1)
=RANK.EQ(B2,$B$2:$B$7,1)
Please pay attention that we lock the range in the ref argument by using absolute cell references, so that it won't change when we copy the formula down the column.
As the result, the lowest value (fastest time) is ranked 1st and the largest value (slowest time) gets the lowest rank of 6. The equal times (B2 and B7) are given the same rank.
How to rank data in Excel uniquely
As pointed out earlier, all of the Excel Rank functions return the same rank for items of equal value. If that's not want you want, use one of the following formulas to resolve tie-break situations and give a unique rank to each number.
Unique ranking from highest to lowest
To rank the math scores of our students uniquely in descending order, use this formula:
=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1
Unique ranking from lowest to highest
To rank the 100-meter race results in ascending order with no duplicates, use this formula:
=RANK.EQ(B2,$B$2:$B$7,1) + COUNTIF($B$2:B2,B2)-1
How these formulas work
As you may have noticed, the only difference between the two formulas is the order argument of the RANK.EQ function: omitted to rank values descending, 1 to rank ascending.
In both formulas, it's the COUNTIF function with the clever use of relative and absolute cell references that does the trick. In short, you use COUNTIF to find out how many occurrences of the number being ranked there are in the above cells, including the number's cell. In the topmost row where you enter the formula, the range consists of a single cell ($B$2:B2). But because you lock only the first reference ($B$2), the last relative reference (B2) changes based on the row where the formula is copied. Thus, for row 7, the range expands to $B$2:B7, and the value in B7 is compared to each of the above cells.
Consequently, for all 1st occurrences, COUNTIF returns 1; and you subtract 1 at the end of the formula to restore the original rank.
For 2nd occurrences, COUNTIF returns 2. By subtracting 1 you increment the rank by 1 point, thus preventing duplicates. If there happen to be 3 occurrences of the same value, COUNTIF()-1 would add 2 to their ranking, and so on.
Alternative solution to break Excel RANK ties
Another way to rank numbers in Excel uniquely is by adding up two COUNTIF functions:
- The first function determines how many values are greater than or less than the number to be ranked, depending on whether you are ranking descending or ascending, respectively.
- The second function (with the "expanding range" $B$2:B2 as in the above example) gets the number of values equal to the number.
For example, to rank numbers uniquely from highest to lowest, you'd use this formula:
=COUNTIF($B$2:$B$7,">"&$B2)+COUNTIF($B$2:B2,B2)
As shown in the screenshot below, the tie-break is successfully resolved, and a unique rank is assigned to each student:
Ranking in Excel based on multiple criteria
The previous example has demonstrated two working solutions for an Excel RANK tie break situation. However, it may seem unfair that equal numbers are ranked differently based solely on their position in the list. To improve your ranking, you may want to add one more criteria to be considered in case of a tie.
In our sample dataset, let's add total scores in column C and calculate rank as follows:
- First, rank with Math Score (main criteria)
- When there is a tie, break it with Total Score (secondary criteria)
To have it done, we will be using a regular RANK/RANK.EQ formula to find rank, and the COUNTIFS function to break a tie:
=RANK.EQ($B2,$B$2:$B$7) + COUNTIFS($B$2:$B$7, $B2, $C$2:$C$7, ">"& $C2)
Compared to the above example, this rank formula is more objective: Timothy is ranked 2nd because his total score is higher than that of Julia:
How this formula works
The RANK part of the formula is obvious, and the COUNTIFS function does the following:
- The first criteria_range/criteria pair ($B$2:$B$7,$B2) counts the occurrences of the value you are ranking. Please notice, we fix the range with absolute references, but do not lock the criteria's row ($B2) so that the formula checks the value in each row individually.
- The second criteria_range/criteria pair ($C$2:$C$7,">"&$C2) finds out how many total scores are greater than the total score of the value being ranked.
Because COUNTIFS works with the AND logic, i.e. counts only cells that meet all of the specified conditions, it returns 0 for Timothy since no other student with the same Math score has a higher total score. Hence, Timothy's rank returned by RANK.EQ is unchanged. For Julia, the COUNTIFS function returns 1 because one student with the same Math score has a higher total, so her rank number is incremented by 1. If one more student had the same Math score and a total score lower than that of Timothy and Julia, his/her rank would be incremented by 2, and so on.
Alternative solutions to rank numbers with multiple criteria
Instead of the RANK or RANK.EQ function, you could use COUNTIF to check the main criteria, and COUNTIFS or SUMPRODUCT to resolve a tie break:
=COUNTIF($B$2:$B$7,">"&$B2) + COUNTIFS($B$2:$B$7, $B2, $C$2:$C$7, ">"&$C2) + 1
=COUNTIF($B$2:$B$7, ">"&$B2) + SUMPRODUCT(--($B$2:$B$7=$B2), --($C$2:$C$7>$C2)) + 1
The result of these formulas are exactly the same as shown above.
How to calculate percentile rank in Excel
In statistics, a percentile (or centile) is the value below which a certain percentage of values in a given dataset falls. For example, if 70% of students are equal to or below your test score, your percentile rank is 70.
To get percentile rank in Excel, use the RANK or RANK.EQ function with a non-zero order argument to rank numbers from smallest to largest, and then divide the rank by the count of numbers. So, the generic Excel Percentile Rank formula goes as follows:
To calculate the percentile rank of our students, the formula takes the following shape:
=RANK.EQ(B2,$B$2:$B$7,1)/COUNT($B$2:$B$7)
To have the results displayed correctly, be sure to set the Percentage format to the formula cells:
How to rank numbers in non-adjacent cells
In situations when you need to rank non-contiguous cells, supply those cells directly in the ref argument of your Excel Rank formula in the form of a reference union, locking the references with the $ sign. For example:
=RANK(B2,($B$2,$B$4,$B$6))
To prevent errors in non-ranked cells, wrap RANK in the IFERROR function, like this:
=IFERROR(RANK(B2,($B$2,$B$4,$B$6)), "")
Please notice that a duplicate number is also given a rank, though cell B5 is not included in the formula:
If you need to rank multiple non-contiguous cells, the above formula may become too long. In this case, a more elegant solution would be defining a named range, and referencing that name in the formula:
=IFERROR(RANK(B2,range), "")
How to rank in Excel by group
When working with entries organized into some kind of data structure, data may belong to various groups, and you might want to rank numbers within each group individually. The Excel RANK function cannot resolve this challenge, so we are going to use a more complex SUMPRODUCT formula:
Rank by group in descending order:
=SUMPRODUCT((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1
Rank by group in ascending order:
=SUMPRODUCT((A2=$A$2:$A$7)*(C2>$C$2:$C$7))+1
Where:
- A2:A7 are groups assigned to numbers.
- C2:C7 are numbers to be ranked.
In this example, we use the first formula to rank numbers in each group from largest to smallest:
How this formula works
Essentially, the formula evaluates 2 conditions:
- First, you check the group (A2=$A$2:$A$7). This part returns an array of TRUE and FALSE based on whether a range element belongs to the same group as A2.
- Second, you check the score. To rank values from largest to smallest (descending order), use the condition (C2<$C$2:$C$11), which returns TRUE for the cells greater than or equal to C2, FALSE otherwise.
Since in Microsoft Excel terms, TRUE = 1 and FALSE = 0, multiplying the two arrays gives an array of 1's and 0's, where 1 is returned only for the rows in which both conditions are met.
Then, SUMPRODUCT adds up the elements of the 1's and 0's array, hence returning 0 for the largest number in each group. And you add 1 to the result to start ranking with 1.
The formula that ranks numbers within groups from smallest to largest (ascending order) works with the same logic. The difference is that SUMPRODUCT returns 0 for the smallest number in a particular group, since no number in that group meets the 2nd condition (C2>$C$2:$C$7). Again, you replace zero rank with the 1st rank by adding 1 to the formula result.
Instead of SUMPRODUCT, you can use the SUM function to add up the array elements. But this would require using an array formula, completed via Ctrl + Shift + Enter. For example:
=SUM((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1
How to rank positive and negative numbers separately
If your list of numbers contains both positive and negative values, the Excel RANK function will rank them all in no time. But what if you'd like to have positive and negative numbers ranked separately?
With numbers in cells A2 to A10, use one of the following formulas to get individual ranking for positive and negative values:
Rank positive numbers descending:
=IF($A2>0,COUNTIF($A$2:$A$10,">"&A2)+1,"")
Rank positive numbers ascending:
=IF($A2>0,COUNTIF($A$2:$A$10,">0")-COUNTIF($A$2:$A$10,">"&$A2),"")
Rank negative numbers descending:
=IF($A2<0,COUNTIF($A$2:$A$10,"<0")-COUNTIF($A$2:$A$10,"<"&$A2),"")
Rank negative numbers ascending:
=IF($A2<0,COUNTIF($A$2:$A$10,"<"&$A2)+1,"")
The results will look something similar to this:
How these formulas work
To begin with, let's break down the formula that ranks positive numbers in descending order:
- In the logical test of the IF function, you check whether the number is greater than zero.
- If the number is greater than 0, the COUNTIF function returns the count of values higher than the number being ranked.
In this example, A2 contains the 2nd highest positive number, for which COUNTIF returns 1, meaning there is just one number greater than it. In order to start our ranking with 1, not 0, we add 1 to the formula result, so it returns a rank of 2 for A2.
- If the number is greater than 0, the formula returns an empty string ("").
The formula that ranks positive numbers in ascending order works a bit differently:
If the number is greater than 0, the first COUNTIF gets the total count of positive numbers in the data set, and the second COUNTIF finds out how many values are higher than that number. Then, you subtract the latter from the former, and get the desired rank. In this example, there are 5 positive values, 1 of which is greater than A2. So, you subtract 1 from 5, thus getting a rank of 4 for A2.
Formulas to rank negative numbers are based on a similar logic.
Note. All of the above formulas ignore zero values because 0 belongs neither to the set of positive nor to the set of negative numbers. To include zeros in your ranking, replace >0 and <0 with >=0 and <=0, respectively, where the formula logic requires.
For example, to rank positive numbers and zeros from largest to smallest, use this formula: =IF($A2>=0,COUNTIF($A$2:$A$10,">"&A2)+1,"")
How to rank data in Excel ignoring zero values
As you already know, a RANK formula is Excel handles all numbers: positive, negative and zeros. But in some cases, we just want to rank cells with data ignoring 0 values. On the web, you can find a few possible solutions for this task, but the Excel RANK IF formula, methinks, is the most universal one:
Rank numbers descending ignoring zero:
=IF($B2=0,"",IF($B2>0,RANK($B2,$B$2:$B$10), RANK($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0)))
Rank numbers ascending ignoring zero:
=IF($B2=0,"",IF($B2>0,RANK($B2,$B$2:$B$10,1) - COUNTIF($B$2:$B$10,0), RANK($B2,$B$2:$B$10,1)))
Where B2:B10 is the range of numbers to be ranked.
The best thing about this formula is that it works beautifully both for positive and negative numbers, leaving zero values out of ranking:
How this formula works
At first sight, the formula may seem a bit tricky. Upon a closer look, the logic is very simple.
Here's how the Excel RANK IF formula ranks numbers from largest to smallest ignoring zeros:
- The first IF checks whether the number is 0, and if it is, returns an empty string:
IF($B2=0,"", …)
- If the number is not zero, the second IF checks whether it's greater than 0, and if it is, a regular RANK / RANK.EQ function calculates its rank:
IF($B2>0,RANK($B2,$B$2:$B$10),…)
- If the number is less than 0, you adjust ranking by the zero count. In this example, there are 4 positive numbers and 2 zeros. So, for the largest negative number in B10, an Excel RANK formula would return 7. But we skip zeros, and therefore we need to adjust the rank by 2 points. For this, we subtract the number of zeros from the rank:
RANK($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0))
Yep, it's that easy! The formula to ranks numbers from smallest to largest ignoring zeroes works in a similar manner, and it could be a good brain exercise to deduce its logic :)
How to calculate rank in Excel by absolute value
When dealing with a list of positive and negative values, there may be a need to rank numbers by their absolute values ignoring the sign.
The task can be fulfilled with one of the bellow formulas, at the heart of which is the ABS function that returns the absolute value of a number:
Rank ABS descending:
=SUMPRODUCT((ABS(A2)<=ABS(A$2:A$7)) * (A$2:A$7<>"")) - SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7)) * (A$2:A$7<>""))+1
Rank ABS ascending:
=SUMPRODUCT((ABS(A2)>=ABS(A$2:A$7)) * (A$2:A$7<>"")) - SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7)) * (A$2:A$7<>""))+1
As the result, negative numbers are ranked as if they were positive numbers:
How to get N largest or smallest values
If you wish to get an actual N number of the largest or smallest values rather than their ranking, use the LARGE or SMALL function, respectively.
For example, we can get the top 3 scores of our students with this formula:
=LARGE($B$2:$B$7, $D3)
Where B2:B7 is the list of scores and D3 is the desired rank.
Additionally, you can retrieve the students' names by using the INDEX MATCH formula (provided there are no duplicate scores in the top 3):
=INDEX($A$2:$A$7,MATCH(E3,$B$2:$B$7,0))
Similarly, you can use the SMALL function to pull the bottom 3 values:
=SMALL($B$2:$B$7, $D3)
That's how you do ranking in Excel. I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Rank formula in Excel - examples (.xlsx file)
149 comments
Hie
I would like to rank my students performance based on whether they passed English and 5 other subjects, and also their total scores. On the total scores, someone might have a higher value but they failed English, so I want excel to rank this one lower than someone who has a lower total value but they passed in English.
Hi! I don't have your data, however, I think you can use the example from the paragraph above for the two ranking criteria: Ranking in Excel based on multiple criteria.
If this does not help, explain the problem in detail.
How would you capture two individuals with the same score (out of 4 individuals) for the top 3 largest/smallest? Using the large formula, it just captures one of them.
Hi! Your question is not quite clear. If the methods suggested in the article above do not work for you, please provide an example of what you want to do.
Hi there.
I am looking to rank golf scores for the same person and if they have the same score more than once, then i can use their inward 9 and the following formula you have provided works well
=RANK.EQ($B2,$B$2:$B$7)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,">"&$C2)
If I want to add a 3rd criteria i.e. best last 6 hole - I tried to repeat the +COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,">"&$C2) but using different columns or the 3rd criteria but i get a #spill error ?
Is this limited to just 2 criteria or can i add a 3rd ?
Cheers
Hi! I cannot determine the cause of the error because I cannot see your data. A third criterion can be added. Maybe this article will be helpful: #SPILL! error in Excel - what it means and how to fix.
How sort and know the first and last in rank
Pay attention to the last paragraph of the article above. If you are not satisfied, please let me know and I will try to help you.
Building a tool for tracking car racing over a season. Points are given to the first 10 places and everyone after 10th receives 0. Tie breaks are resolved by who has the most first places up to that point in the season. Then second. Then third. And so on until ties are resolved.
Your formula above for multiple criteria, RANK.EQ(Points to date)+COUNTIFS(Race #1 finishing place), works for breaking the tie of cars 11-20 for the first race of the season because every driver's result in that race would their overall rank. For subsequent races I can't figure out how to make that work.
I've made helper tables I can reference for each of the races, with each of the possible positions, for each of the drivers. Table 2 being the cumulative of race 1 and race 2, and so forth.
I got it to work with RANK.EQ + a COUNTIF for each finishing place, but I don't know how to make it stop when the tie is broken.
Apologies for the long code below. It's one RANK.EQ followed by 20 COUNTIF statements. The error I'm working against, I need it to stop after the 3rd COUNTIF (H$558:H$579) because the tied drivers were matched for 1st and 2nd places.
=RANK.EQ(JT3,JT$3:JT$24)+COUNTIFS(JT$3:JT$24,JT3,D$558:D$579,">"&D558)+COUNTIFS(JT$3:JT$24,JT3,F$558:F$579,">"&F558)+COUNTIFS(JT$3:JT$24,JT3,H$558:H$579,">"&H558)+COUNTIFS(JT$3:JT$24,JT3,J$558:J$579,">"&J558)+COUNTIFS(JT$3:JT$24,JT3,L$558:L$579,">"&L558)+COUNTIFS(JT$3:JT$24,JT3,N$558:N$579,">"&N558)+COUNTIFS(JT$3:JT$24,JT3,P$558:P$579,">"&P558)+COUNTIFS(JT$3:JT$24,JT3,R$558:R$579,">"&R558)+COUNTIFS(JT$3:JT$24,JT3,T$558:T$579,">"&T558)+COUNTIFS(JT$3:JT$24,JT3,V$558:V$579,">"&V558)+COUNTIFS(JT$3:JT$24,JT3,X$558:X$579,">"&X558)+COUNTIFS(JT$3:JT$24,JT3,Z$558:Z$579,">"&Z558)+COUNTIFS(JT$3:JT$24,JT3,AB$558:AB$579,">"&AB558)+COUNTIFS(JT$3:JT$24,JT3,AD$558:AD$579,">"&AD558)+COUNTIFS(JT$3:JT$24,JT3,AF$558:AF$579,">"&AF558)+COUNTIFS(JT$3:JT$24,JT3,AH$558:AH$579,">"&AH558)+COUNTIFS(JT$3:JT$24,JT3,AJ$558:AJ$579,">"&AJ558)+COUNTIFS(JT$3:JT$24,JT3,AL$558:AL$579,">"&AL558)+COUNTIFS(JT$3:JT$24,JT3,AN$558:AN$579,">"&AN558)+COUNTIFS(JT$3:JT$24,JT3,AP$558:AP$579,">"&AP558)
Hi! Unfortunately, this information is not enough to understand what you need.
There's a lot of parts so I'll try and break it down as succinctly as possible. The 22 drivers that competed are listed in rows 3-24. Races are in the columns with multiple data points for each race, e.g., Race 1 is columns D-M. Race 2 is columns N-X. And so on for 23 races.
Vital columns for this exercise are columns I and S (The points total up to that point in the season, and columns E and O, their finishing positions in those first two respective races. Columns J and T are intended to be the calculated season ranks for each driver after the corresponding race.
Scoring gives points to the top 10 finishers, decreasing from 25 points for first, and 1 point for 10th. Places 11 and higher receive zero points. Tie breaking rules give the top rank to the driver with the most finishes in the highest place.
After the very first race, there is 10 drivers with points, and places 11-20 are tied on points.
I'm able to resolve the tie after the first race with: J3 = RANK.EQ(I3,I$3:I$24)+COUNTIFS(I$3:I$24,I3,E$3:E$24,""&D35)+COUNTIFS(S$3:S$24,S8,F$30:F$51,">"&F35)+COUNTIFS(S$3:S$24,S8,H$30:H$51,">"&H35)+COUNTIFS(S$3:S$24,S8,J$30:J$51,">"&J35)
When we get to the fourth place tie breaker for 20 points.... Neither driver has had a first, second or third place finish for the first three COUNTIFS statements. Driver #8 has had 1 fourth place and Driver #5 has had none. Driver #5 has +1 added to his 4th place rank to put him in 5th place.
Unfortunately, for the formula above to be applied to the whole group of drivers, all 20 COUNTIFS statements are included, not just the first 4. So when the 5th COUNTIF statement is looked at, Driver #5 has had 2 fifth place finishes and Driver #8 has had none. The formula adds one to Driver 8, and they're both sitting at Rank 5 now.
I either need to rewrite the long COUNTIF with some form of logic so it stops comparing the drivers after a resolution is found, or I need to take a different approach. I assume the helper tables, are necessary considering the cumulative nature of measuring placement across the whole season, but I'm also open to other approaches. Of all the other F1 spreadsheets I've found online, I haven't found anyone automating ranking.
Hi! This is a complex solution that cannot be found with a single formula. Without seeing your data, it is impossible to understand this problem. If you have a specific question about the operation of a function or formula, I will try to answer it.
Hi there.
I am looking for a formula where it can rank someone based on the best time with the least amount of penalties.
Is there perhaps one that I can use?
I have tried a multitude of formulas, but it just doesn't want to work out.
Some give me a rank that is higher than the amount of participants and others just gives first place to everyone.
Kind regards.
Hi! To understand what you want to do, give an example of the source data and the expected result.
Hello sir,
I used the rank function for a column
But the result is:
1
1
1
4
5
6
I need to the 4 to be 2 and 5 to be 3
Thank you
Hi! To determine the ascending rank in a list with repeats, try this formula:
=COUNT(1/FREQUENCY($A$2:$A$15,IF($A$2:$A$15<A2,$A$2:$A$15)))
I am sorry , are you sure that this formulas is completed ?
Let me clarify to you the formulas that i am using is
=Rank(C17,$C$17:$C$38,1)
In the result I have some cells with ranking 1 let say that I have 4 cells result with number 1 , then the nearst number from 1 is 5 , I need it to be 2 and 6 to be 3. how can I fix this ?
Hi! Unfortunately, the formula was not written down completely. Corrected. Check with your data.
The result on the column is zero
I am sorry but this function didn’t solve my problem!
Its give me zero’s in all column
Is there ither formula ? Or csn you check it agian ?
Thank you sir
This formula works with the data you gave earlier:
=COUNT(1/FREQUENCY($A$2:$A$15,IF($A$2:$A$15<A2,$A$2:$A$15)))
Hi, sir
I want your kind help to solve my problem in excel.
I have prepared a final marks sheet and sort that in largest to smallest. Now I want to give merit numbers. If two or more nominees scored equal marks it should be same merit number. Please guide me how can I do this
Hi! To understand what you want to do, give an example of the source data and the expected result.
Hi there,
How to calculate if the rank in the bottom of 3 and I need to included amount of punishment?
Could you help me please?
Thanks in advance🙏🏻
To understand what you want to do, give an example of the source data and the desired result.
I can perform the Rank Formula, but I am stumped on calculating the "deficit behind the leader" For example if 3rd place is 50 points behind leader. Leader will continually change so I need to formula. Any help would be appreciated.
Sales Rank Deficit behind Leader
50 3 50
100 1 0
75 2 25
Hi!
To determine the leader value, use the MAX function
=MAX($A$1:$A$10)-A1
This should solve your task.
This is a superbly written article. I wish more technical writers had the ability to fully explain items rather than gloss over the details or ignore them entirely.
Hi Sir, May I ask what formula should I use the condition I face?
For example, for those employee that make sales above RM 10,000, the company will benefits them RM 100, RM 50, RM20 according to their sales made ranking?
Hi!
If I understand the problem correctly, you may find this article helpful: Nested IF in Excel – formula with multiple conditions.
Hello,
How can I do this query? I have to use RANK and SUMPRoduct?
Rank top 5 categories by sales in Quarter 1
No Category
1
2
3
4
5
Hello!
The following tutorial should help: Excel formula to find top 3, 5, 10 values in column or row. The category that corresponds each value can be found using INDEX MATCH.
I hope my advice will help you solve your task.
Dear Sir/Madam,
I need you kind consideration and support on the following matter.
I wan to find out the rank based on two condition. I'm preparing a master mark sheet for my students in examination. My conditions are that the ranking is based on if the students passes in english subject plus the overall. if the students fail english but pass overall than the ranking will be done after the ranking the students who have passed english and overall.
Eg. Eng (/100) Overall (/400) Expected Rank to
1. Raj 45 350 3rd
2. Krish 50 300 1st
3. Chand 30 180 4th
4. David 60 150 5th
5. Ping 65 260 2nd
As show 350 is ranked after ranking 300 and 260 because in both cases english is a pass while in 350 the students failed english. Also in 1st position einglish is lower than in 2nd position. This is ok! the overall is first ranked from highest to lowest as long as english pass than the other overall marks are ranked.
Hi!
Unfortunately your ranking criteria is not clear to me. To rank by two columns B and C you can try this formula
=RANK(B2,$B$2:$B$7)+SUMPRODUCT(--($B$2:$B$7=$B2),--(C2<$C$2:$C$7))
Is there a way, when having a ranking system based on non quantitative data, to change the rank of one item and have the rest of the items in the list change rank accordingly?
For example:
Dogs 1
Cats 2
Frogs 3
Horse 4
If I want to change the rank of Frogs to 1, is there a way to automatically change the ranking of Dogs to 2 and Cats to 3, as well as have it be scalable to a dataset with hundreds of rows?
Hi,
I have a sheet were a person is ranked based on a test result (beginner, intermediate, expert, etc). This test can be repeated several times and I need a vlookup to find the highest ranking achieved, based on this custom ranking.
TIA
Hello!
Try to use the recommendations described in this article: Excel MAXIFS function - get largest value based on multiple criteria.
This should solve your task.
Hi Alexander,
Thank you for the reply.
I had a look at the article and it does not seem to help in this instance - I might not have explained my problem correctly.
My test result sheet is as follows:
Joe 10/10/21 Beginner
Peter 10/10/21 Beginner
John 10/10/21 Beginner
Joe 11/11/21 Beginner
Peter 11/11/21 Beginner
John 11/11/21 Intermediate
Joe 12/12/21 Beginner
Peter 12/12/21 Intermediate
John 12/12/21 Expert
I need a formula that will return the following values (when I type in the names) on another sheet in the same book:
Joe Beginner
Peter Intermediate
John Expert
I trust this help and look forward to your assistance.
Jacques
Hi!
Find the maximum date for a specific person with the MAXIFS function. Then use the INDEX+MATCH functions to get the corresponding value.
=INDEX($C$1:$C$10,MATCH(1,($A$1:$A$10=$E$1)* ($B$1:$B$10=MAXIFS($B$1:$B$10,$A$1:$A$10,$E$1))))
This should solve your task.
Thank you.
I figured out a workaround to look for the maximum number and then do the classification again...my formula now looks like a thesis.
Using Office 2013 also compounded the problem.
Thank you once again for your assistance - much appreciated.
Jacques
Hi,
I got the Rank without Duplicates using =RANK.EQ(G2,$G$2:$G$31,0)+COUNTIF($G$2:G2,G2)-1
but ranks are applied for students who Failed. so i combined with =IF(H2="Pass",RANK.EQ(G2,$G$2:$G$31,0)+COUNTIF($G$2:G2,G2)-1,"NO RANK")
but now it skips the rank
e.g...
g h i
total Result Rank
413 pass 1
339 fail no rank
336 pass 3
is there any solution for this?
Hello!
Although Excel has a RANK function, it doesn't have a RANGEIF function for conditional ranking. So use the COUNTIFS function In a formula like this:
=IF(H2="pass",COUNTIFS($H$2:$H$10,"pass",$G$2:$G$10,">"&G2)+1,"no rank")
I hope my advice will help you solve your task.
works perfectly... Thank you so much...
Hello
I want to rank these numbers (18,16.5,8,20,17,19,19,17.5,18,20). If two numbers have the same rank (20=1), 19 must be (19=2) and 18= 3.
What formula can do that?
Thanks in advance
Thank you so much
Topic - How to get N largest or smallest values
Student Score
Daniela 79
Tommy 79
Edward 90
Julia 88
Timothy 89
Peter 75
Bottom 3
Score Name
1 75 Peter
2 79 Daniela
3 79 Daniela
79 Score will be repeated name, it is not taking next name in Index Formula. How to correct it.
Hi!
You can find the examples and detailed instructions here: How to find top values with duplicates.
Hope this is what you need.
Hi there,
I have used the rank eq function to rank the number of stores as per their sales values. What I'm trying to do is allocate stock to these stores. I have applied the logic but there is remainder stock that I want to be able to allocate to the top 10 ranked stores. For example if I have 200 units left, I want to be able to allocate 10 out of this 200 to each of the top 10 stores in a loop. 1st store gets 10, then 2nd, then 3rd till the time 200 becomes zero. HELP!!
Cheers
Hello!
I don't know how you determine the rank, but a formula with an IF function might look like this:
=IF(RANK($A2,$A$2:$A$100) < 11,B2+200*0.1,B2)
I hope it’ll be helpful.
Hi
I am wondering if there is a formula I can use to show the ranking slightly differently to how this formula is working. In the example below, there are 18 records and ideally I would like the first three to show 18, then then the next two as 15, then the next three as 13 and so forth. The way it is working currently is that it is ordering up from 1, so if has four values as 1, then the next group as 5. So I am looking for similar logic, but having the ranking start from the higher numbers, not the lower numbers. If this is not clear, please let me know.
Best result adjusted Sum Product Rank
-0.95 16
-0.95 16
-0.95 16
-0.9 14
-0.9 14
-0.85 11
-0.85 11
-0.85 11
-0.8 8
-0.8 8
-0.8 8
-0.75 5
-0.75 5
-0.75 5
-0.7 1
-0.7 1
-0.7 1
-0.7 1
Hello!
Use the RANK.EQ function, a detailed description of which you can see in the article above.
=RANK.EQ(A2,$A$2:$A$19,0)
Сopy this formula down along the column.
Hello - I am trying to rank race times and then ultimately give the ranked position a point value. For example there are 3 competitors with times: 12.8, 13.5, 10.1 and I want to rank them with 1st, 2nd, 3rd or 1,2,3; then give 1st = 10 pts, 2nd= 9 pts, 3rd = 8 pts. What formula would I use or how would I go about getting to the ultimate place of points? I made a column with the ranking using this formula I found: =IF($B4=0,"",IF($B4>0,RANK($B4,$B$4:$B$22,1) - COUNTIF($B$4:$B$22,0), RANK($B4,$B$4:$B$22,1)))
But now I need to create the points associated with the rank. Thank you. Bella
Hi!
To determine the scores you can use the formula -
=11-RANK(A1,$A$1:$A$3,1)
Hope this is what you need.
Hello friends. Please I want to rank my students whose individual records are contained in different sheets of same workbook and know who comes 1st, 2nd 3rd....etc. can't seem to solve it. Please help.
Hello!
Pay attention to the following paragraph of the article above - How to rank numbers in non-adjacent cells.
Use external references to other worksheets in your book.
I have positive and negative values for planet strength for 9 planets..I want Ranking considering both positive and negative values..
Kethu 865 420
Jupiter 1464 551
Rahu 1389 666
Mercury 1282 840
Venus 923 385
Sun 728 460
Moon 1200 611
Mars 1293 596
Saturn 1406 556
Rank is required..Kindly advise..
Hello!
If I understand your task correctly, pay attention to the paragraph in this article - How to calculate rank in Excel by absolute value
Hello, I tried to apply the formula which worked for rounded values but doesnt seem to work with others :
It worked with this list :
66
94
77
99
106
82
113
75
90
96
65
95
But not with these values :
Turnover £1m
20.5
26.9
4.6
47.8
9
37.1
19.8
31.5
28
22.5
28.9
21.2
19
24.7
38.9
41.5
14
22.7
28.9
12.3
Is there another formula? Cause it keeps on saying NO VALUE when I try to do the RANK.AVG formula
Thank you very much
Hello!
I cannot guess which formula you are using. If all of your values in the list are numbers, then the RANK.AVG function works correctly.
i am facing a problem. problem is that i have a marks sheet of students in which mostly we have same mark of students. i need same position against of same marks and next highest position should be second but that shows third instead of 2nd.... need help
Hello!
To determine the rank in a list with duplicate values, use the formula
=RANK(A2,$A$2:$A$15,0)+COUNTIF(A$2:A2,A2)-1
This should solve your task.
not working sir.... let me give you example
obtain marks Perc% Rank expecting
419 99.76 1 1
419 99.76 1 1
418.5 99.64 3 2
418.5 99.64 3 2
414 98.57 5 3
i need rank as 1-1-2-2-3
Hi!
To determine the rank in a list with duplicate values, use the formula:
=COUNT(1/FREQUENCY($A$2:$A$15,IF($A$2:$A$15>=A2,$A$2:$A$15)))
In Excel2019 and below, you must enter as an array formula.
Hi,
This is excellent thanks for sharing, is there a way to modify the option "How to calculate rank in Excel by absolute value" to be based on another criteria, e.g. rank absolute value by department (I have blanks in the data which need lowest value given and then the lowest actual number with a 2 etc)?
Any guidance which would hugely appreciated
BW
Mark
Hello!
To rank by department, you can use this section above: How to rank in Excel by group.
Hi,
Thanks for the resposne.
I have tried this and my data has ties and blanks within it, using the option you suggested doesn't provide a dense rank unfortunately. It skips values when there is a tie.
Is there a way of modifying it so a tie would return the same value, then the next would increment by 1 (with zero always being the lowest returned value as I'm raking in descending order)?
Thanks I really appreciate your assistance
BW
Mark
Sorry I meant to state 1 as the lowest rank returned, which blanks should be allocated and then a 1 to the lowest number that isn't blank :-)
Hi!
I could not find the problems you are talking about. The same results do not affect the ranking. The resulting ranks are, for example, 1, 1, and 3. Clarify your problem.
Thanks for coming back to me, I've tried two formulas to do this including your suggestion which is appreciated.
Say I have a set up results as follows, what I need is for the rank not to skip a value in a tie situation (dense rank).
So the top rows would read, 1, 2, 2, 3, 3, 4, 4 as opposed to 1, 2, 2, 4, 4, 6, 6
race_id race_rpr RPR Rank2 RPR Rank 3
788256 74 1 1
788256 73 2 2
788256 73 2 2
788256 72 4 4
788256 72 4 4
788256 71 6 6
788256 71 6 6
788256 70 8 8
788256 70 8 8
788256 70 8 8
788256 69 11 11
788256 69 11 11
788256 68 13 13
788256 67 14 14
788256 63 15 15
Hopefully this makes sense, the fomulas I'm using are in a table, both are delivering the same result
RPR Rank 1 =SUMPRODUCT(($B2=[race_id])*(W2"&[@[race_rpr]],[race_id],[@[race_id]])+1
Hello!
To calculate the dense rating for a group, use the formula
=SUMPRODUCT((FREQUENCY(IF($A$1:$A$15=A1,$B$1:$B$15),$B$1:$B$15)>0)*($B$1:$B$16>=B1))
This is an array formula. Press Ctrl + Shift + Enter so that array function works.
Alexander, I was trying to repurpose your SUMPRODUCT((FREQUENCY piece for my application but I'm not quite getting what I'm after. I feel like arrays have always been my weakest area in Excel. Here's my table. Column D is what I'm after, column E is what my poorly reworked array is returning. Cell E2 is this and is dragged down for the rest of column E. What am I doing wrong?
P.S. I'm a fan of yours and Svetlana's. I consider myself now a pro in Excel for the most part but occasionally I get stumped. Sure enough, after a quick google search, I always end up at one of your articles which clears things up for me. This has happened consistently over the past 10 years. Pass on my thanks for that!
In cell E2
{=SUMPRODUCT((FREQUENCY(IF($B$1:$B$19=B2,$C$1:$C$19),$C$1:$C$19)>0)*($C$1:$C$19<C2))+1}
RECORD;BILL_NUMBER;PO_NUMBER;SHOULD_RETURN;RETURNS_THIS
1;B5678;300006;1;1
2;B5678;300006;1;1
3;INV1234;300004;1;1
4;INV1234;300004;1;1
5;INV1234;300014;3;4
6;INV1234;300004;1;1
7;INV1234;300005;2;3
8;X5555;300009;1;2
9;INV1240;300001;1;1
10;INV1240;300001;1;1
11;INV1240;300002;2;2
12;5678910;300012;2;2
13;5678910;300011;1;2
14;5678910;300011;1;2
15;B5700;300013;1;2
16;I4529;300008;2;1
17;I4529;300007;1;1
18;I4529;300007;1;1
Hi!
Sorry, it's not quite clear what you are trying to achieve. What do the numbers in column D mean?
Sorry if I was unclear, so column D is what I want it to return. What I'm after is a suffix for the bill number. So ultimately it would return something like INV1234-1, INV1234-1, INV1234-3, INV1234-1 & INV1234-2 for that bill number. So column D is the rank of distinct PO_NUMBER for each BILL_NUMBER. For INV1234, there are 3 distinct PO_NUMBERs (300004, 300005, 300014), thus the suffixes are -1, -2 & -3.
The reason for this is because we are converting POs to Vendor Invoices (Bill), A single Bill is matched to 3 separate POs and we can't convert the 3 POs to one Invoice. We also can't create three invoices with the same exact invoice number. We have to convert the 3 lines on PO 300004 to a vendor invoice with bill number INV1234-1. Then convert the 1 line on PO 300005 to vendor invoice INV1234-2. Then convert the 1 line on PO 300014 to vendor invoice INV1234-3.
Hi!
This formula determines the rank by the condition - the same values in column B.
=COUNT(1/FREQUENCY($C$1:$C$18*($B$1:$B$18=B1),IF($C$1:$C$18>=C1,$C$1:$C$18)))
Highest rank at maximum values. The same values have the same rank.
Hmm, not sure If I'm just not getting this. I tried the formula you specified,
=COUNT(1/FREQUENCY($C$1:$C$18*($B$1:$B$18=B1),IF($C$1:$C$18>=C1,$C$1:$C$18)))
in cell E1 and ctrl+shift+entered then dragged down but it is returning zeros for all. I wasn't sure if the issue was in the Bill Number being a string instead of value so tried with value only but it still was returning all zeros. Do I not need a SUMPRODUCT in there somwhere?
Hi!
Column B - BILL_NUMBER
Column C - PO_NUMBER
Formula tested on OFFICE365
Hmm, that is odd. I'm on Mac desktop client 16.61.1. It seems to behave the same as PC with Arrays. I prefer PC but maybe I should check on my other computer.
I tried on Excel 365 and Windows Excel (current version). The formula seems to only be returning zeros?
=COUNT(1/FREQUENCY($C$1:$C$18*($B$1:$B$18=B1),IF($C$1:$C$18>=C1,$C$1:$C$18)))
Hi!
Here is the result that the formula returns for your data.
1;1;3;3;1;3;2;1;2;2;1;1;2;2;1;1;2;2
I have data for which I have calculated their mean, median, mode, standard deviation. how do I calculate their rank? should I only use mean (highest mean would be rank 1 and so on ) or should I use ''mean divided by standard deviation, i.e. signal to noise ratio (snr) '' to calculate their rank in excel? please help!
this is the data that I need help with.
MEAN SD
4.4 0.753937035
4.45 0.944513241
4.05 1.145931017
4.75 0.550119604
4.5 0.827170192
4.35 0.74515982
4.45 0.887041208
4.05 0.944513241