SOLVED
Home

Problem in Rank command

%3CLINGO-SUB%20id%3D%22lingo-sub-309384%22%20slang%3D%22en-US%22%3EProblem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309384%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20I%20am%20a%20school%20teacher%20working%20in%20excel%20for%20creating%20rank%20sheet%20of%20students%20based%20on%20their%20subject%20score%20but%20I%20found%20a%20problem%20and%20I%20have%20no%20solution%20to%20it.%20if%20anyone%20has%20kindly%20guide%20me.%3C%2FP%3E%3CP%3EThis%20is%20the%20command%20I%20use%3C%2FP%3E%3CP%3E%3DIF(K8%3D0%2CRANK(H8%2C%24H%248%3A%24H%2439%2C0)%2C%22--%22)%3C%2FP%3E%3CP%3Eit%20gives%20the%20rank%20but%20the%20final%20rank%20is%20one%20number%20more%20(it%20should%20be%208%20but%20it%20shows%209)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-309384%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-360024%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-360024%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20instance%20I%20think%20it%20is%20possible%20to%20do%20away%20with%20helper%20columns%20by%20using%20COUNTIFS%20to%20rank%20rather%20than%20the%20RANK%20function%20itself.%26nbsp%3B%20The%20information%20you%20need%20for%20that%20has%20already%20been%20collected%20in%20the%20'No.%20of%20Fails'%20and%20'Total'%20columns.%3C%2FP%3E%3CP%3E%3D%20IF(%20NOT(Fails)%2C%201%20%2B%20COUNTIFS(%20Fails%2C%200%2C%20Total%2C%20%22%26gt%3B%22%20%26amp%3B%20Total%20)%2C%20%22--%22%20)%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314127%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314127%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20wyn%20for%20your%20kind%20guidance%20but%20i%20cant%20get%20the%20names%20who%20got%20%26lt%3B35%20marks%20only%20in%20one%20subject.%20to%20explain%20correctly%20i%20created%20a%20table%20in%20my%20excel%20sheet%20named%20%22Single%20subject%20failures%22%20with%20all%20subject%20names.%20what%20i%20need%20is%2C%20for%20example%20the%20second%20column%20shows%20English.%20so%20that%20column%20should%20display%20the%20name%20of%20the%20students%20got%20%26lt%3B35%20marks%20in%20english%20and%20got%20%26gt%3B35%20marks%20in%20all%20other%20subjects.%20i%20tried%20pivot%20table%2C%20vlookup%20and%20others%20i%20cant%20find%20a%20solution.%20please%20give%20me%20a%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKarthic%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314042%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314042%22%20slang%3D%22en-US%22%3EHi%20I%20would%20add%20a%20column%20with%20a%20formula%20like%20%3DCOUNTIFS(%20RowOfResults%2C%20%22%26lt%3B35%22)%20then%20copy%20that%20formula%20down%20and%20then%20do%20a%20single%20%3DCOUNTIFS(%20ColumnOfFormula%2C%201%20)%20that%20would%20count%20how%20many%20times%20the%20COUNTIFS%20%26lt%3B%2035%20resulted%20in%20exactly%201%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313798%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313798%22%20slang%3D%22en-US%22%3EDear%20sir%3CBR%20%2F%3ECan%20you%20guide%20me%20to%20select%20the%20names%20of%20the%20students%20got%20less%20than%2035%20marks%20in%20only%20one%20subject%20in%20the%20above%20file.%20i%20tried%20diff%20methods%20for%20the%20last%20one%20week%20i%20cant%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310176%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310176%22%20slang%3D%22en-US%22%3Esure%20sir%20i%20will%20also%20try%20grouping%2C%20am%20learning%20day%20by%20day.%20for%20learning%20i%20asked%20other%20ways.%20if%20you%20can%20please%20guide%20me%20what%20other%20commands%20can%20be%20used%20in%20this%20case%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310108%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310108%22%20slang%3D%22en-US%22%3EI'm%20a%20big%20fan%20of%20adding%20extra%20columns%20to%20keep%20formula%20as%20simple%20as%20possible%2C%20the%20helper%20columns%20can%20always%20be%20hidden%20(grouped)%20if%20presentation%20is%20the%20main%20concern%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310107%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310107%22%20slang%3D%22en-US%22%3Eits%20a%20extra%20column%20no%20need%20of%20it%20in%20that%20sheet%20sir%2C%20so%20i%20asked.%20Thank%20you%20once%20again%20sir%20for%20your%20guidance%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310079%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310079%22%20slang%3D%22en-US%22%3ENot%20that%20I%20can%20think%20of%20sorry.%20Is%20there%20a%20reason%20you%20cannot%20add%20an%20extra%20column%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310054%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310054%22%20slang%3D%22en-US%22%3EThank%20you%20sir%20i%20got%20clear%20idea%20now%20but%20is%20there%20any%20other%20way%20or%20formula%20to%20calculate%20without%20an%20extra%20column%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310046%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310046%22%20slang%3D%22en-US%22%3ESo%20the%20rank%20formula%20is%20being%20performed%20regardless%20of%20your%20IF%20statement.%20The%20Rank%208%20is%20achieved%20by%20student%203.%20it%20is%20not%20displayed%20since%20the%20IF%20statement%20is%20applied%20and%20therefore%20%22--%22%20is%20shown%20instead%20of%208.%20%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20avoid%20the%20RANK%20being%20calculated%20on%20%22ineligible%22%20entries%20then%20the%20score%20needs%20to%20be%20set%20to%200%20-%20hence%20I%20added%20a%20new%20column%20doing%20this.%20The%20RANK%20is%20then%20worked%20out%20on%20this%20new%20column%20of%20%22eligible%22%20scores.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310044%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310044%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20you%20guidance%20sir%20but%20till%20now%20i%20cant%20understand%20the%20problem%20if%20possible%20can%20you%20explain%20it%20more%20clearly%20why%20it%20not%20worked%20previously%20and%20now%20working%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310023%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310023%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20your%20issue%20is%20that%20student%20number%203%20is%20ranked%208%2C%20but%20the%20IF%20statement%20is%20excluding%20them%20so%20the%208%20is%20not%20displaying.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20the%20ranking%20to%20apply%20after%20applying%20the%20IF%20checks%20then%20I'd%20add%20an%20extra%20column%20to%20change%20the%20score%20to%200%20if%20the%20conditions%20aren't%20met.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Esee%20attached%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309779%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309779%22%20slang%3D%22en-US%22%3E%3CP%3ESIr%20i%20am%20attaching%20my%20file%20with%20this%20please%20guide%20me%20to%20rectify%20the%20problem%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309560%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20in%20Rank%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309560%22%20slang%3D%22en-US%22%3EAre%20you%20able%20to%20attach%20a%20file%20with%20a%20copy%20of%20the%20values%20you%20have%20in%20H8%20to%20H39%20%3F%3C%2FLINGO-BODY%3E
Karthicr
Occasional Contributor

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
Are you able to attach a file with a copy of the values you have in H8 to H39 ?

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

Solution

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

 

 

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

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.
Thank you sir i got clear idea now but is there any other way or formula to calculate without an extra column
Not that I can think of sorry. Is there a reason you cannot add an extra column?
its a extra column no need of it in that sheet sir, so i asked. Thank you once again sir for your guidance
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
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
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
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

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

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