Jan 06 2019 01:17 AM
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)
Jan 06 2019 11:31 PM
Jan 07 2019 07:44 AM
SIr i am attaching my file with this please guide me to rectify the problem
Jan 07 2019 04:15 PM
SolutionSo 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
Jan 07 2019 05:19 PM
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
Jan 07 2019 05:24 PM
Jan 07 2019 05:48 PM
Jan 07 2019 08:15 PM
Jan 07 2019 10:57 PM
Jan 07 2019 11:13 PM
Jan 08 2019 03:47 AM
Jan 11 2019 09:08 AM
Jan 12 2019 02:09 AM
Jan 12 2019 09:24 AM
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
Mar 04 2019 11:55 AM
Hi @Wyn Hopkins
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
Jan 07 2019 04:15 PM
SolutionSo 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