• 510K Members
• 6,859 Online
• 607K Conversations
SOLVED

Problem in Rank command

Occasional Contributor

Problem in Rank command

Hello I am a school teacher working in excel for creating rank sheet of students based on their subject score but I found a problem and I have no solution to it. if anyone has kindly guide me.

This is the command I use

=IF(K8=0,RANK(H8,\$H\$8:\$H\$39,0),"--")

it gives the rank but the final rank is one number more (it should be 8 but it shows 9)

14 Replies

Re: Problem in Rank command

Are you able to attach a file with a copy of the values you have in H8 to H39 ?

Re: Problem in Rank command

SIr i am attaching my file with this please guide me to rectify the problem

Solution

Re: Problem in Rank command

So your issue is that student number 3 is ranked 8, but the IF statement is excluding them so the 8 is not displaying.

If you want the ranking to apply after applying the IF checks then I'd add an extra column to change the score to 0 if the conditions aren't met.

see attached

Re: Problem in Rank command

Thank you for you guidance sir but till now i cant understand the problem if possible can you explain it more clearly why it not worked previously and now working

Re: Problem in Rank command

So the rank formula is being performed regardless of your IF statement. The Rank 8 is achieved by student 3. it is not displayed since the IF statement is applied and therefore "--" is shown instead of 8.

To avoid the RANK being calculated on "ineligible" entries then the score needs to be set to 0 - hence I added a new column doing this. The RANK is then worked out on this new column of "eligible" scores.

Re: Problem in Rank command

Thank you sir i got clear idea now but is there any other way or formula to calculate without an extra column

Re: Problem in Rank command

Not that I can think of sorry. Is there a reason you cannot add an extra column?

Re: Problem in Rank command

its a extra column no need of it in that sheet sir, so i asked. Thank you once again sir for your guidance

Re: Problem in Rank command

I'm a big fan of adding extra columns to keep formula as simple as possible, the helper columns can always be hidden (grouped) if presentation is the main concern

Re: Problem in Rank command

sure sir i will also try grouping, am learning day by day. for learning i asked other ways. if you can please guide me what other commands can be used in this case

Re: Problem in Rank command

Dear sir
Can you guide me to select the names of the students got less than 35 marks in only one subject in the above file. i tried diff methods for the last one week i cant

Re: Problem in Rank command

Hi I would add a column with a formula like =COUNTIFS( RowOfResults, "<35") then copy that formula down and then do a single =COUNTIFS( ColumnOfFormula, 1 ) that would count how many times the COUNTIFS < 35 resulted in exactly 1

Re: Problem in Rank command

Thank you wyn for your kind guidance but i cant get the names who got <35 marks only in one subject. to explain correctly i created a table in my excel sheet named "Single subject failures" with all subject names. what i need is, for example the second column shows English. so that column should display the name of the students got <35 marks in english and got >35 marks in all other subjects. i tried pivot table, vlookup and others i cant find a solution. please give me a solution.

Karthic

Re: Problem in Rank command

In this instance I think it is possible to do away with helper columns by using COUNTIFS to rank rather than the RANK function itself.  The information you need for that has already been collected in the 'No. of Fails' and 'Total' columns.

= IF( NOT(Fails), 1 + COUNTIFS( Fails, 0, Total, ">" & Total ), "--" )

Peter