PLS;HELP TO RANK STUDENTS POSITION

[Guest]

I'm class teacher from Malaysia. I'm try to find students position from 3 classes using rank. I able to find student positions for that class, but dont know how to find student positions compare with another 2 classes. Example
-------------------------------------------------------------------------------------------------------------------------------
class: 1love.xl
student Total Marks (%) Position in Class Position in For
A 58 =RANK(B2,B2:B4) ??? *need to compare with another 2 classes(below
B 98
C 78
--------------------------------------------------------------------------------------------------------------------------------
class: 1Joy.xl
student Total Marks (%) Position in Class Position in For
A 85 =RANK(B2,B2:B5) ??? *need to compare with another 2 classes(above and below
B 72
C 4
D 6

------------------------------------------------------------------------------------------------------------------------------------
class: 1Hope.xl

student Total Marks (%) Position in Class Position in For
A 33 =RANK(B2,B2:B6) ??? *need to compare with another 2 classes(ABOVE
B 63
C 6
D 4
E 93

 

[Harlan Grove]

I'm class teacher from Malaysia. I'm try to find students position from 3
classes using rank. I able to find student positions for that class, but dont
know how to find student positions compare with another 2 classes. Example:

...

If you want to compare students by average marks in all classes, then copy the
lists of students and scores into a single master list ideally in another
worksheet. This master list should include top row field names for class,
student and score, and I'll assume they're in columns A through C, and for my
own convenience I'll also assume they span rows 2 to 100. Enter the same
fieldname for student in the row 1 cell in another column, which I'll assume is
column G. Then select the entire master list and use Data > Filter > Advanced
Filter, select to copy to another area, leave the criteria field blank, enter G1
as the address for the output range, check the unique records box, and click OK.
Column G should now contain all distinct student IDs.

In the column to the right of the distinct student ID list, so column H in my
example, in row 2 (corresponding to the topmost student's ID) enter the array
formula

=AVERAGE(IF($B$2:$B$100=G2,$C$2:$C$100))

fill H2 down as far as needed, or just double click on the fill handle. Use the
average scores in column H to rank students.

 

[Harlan Grove]

Thanks for your help.
I try to follow as you mention. I still cannot find position for my students
overall. Is that any formula to calculate without copyto another worksheet?

You could try the THREED function in Laurent Longre's MOREFUNC.XLL add-in, which
is freely available from

http://longre.free.fr/english/

Then if all classes started in cell A1 in their respective worksheets with
headings in the top row, student IDs in col A and numeric scores in col B, with
the class with the largest number of student having, say, 30 students, you could
create a list of the distinct student IDs in a different worksheet using the
following formulas.

A1:
Student

A2:
=FirstClass!A2

A3: <ARRAY FORMULA - press [Ctrl]+[Shift]+[Enter] to enter it.>
=INDEX(THREED(FirstClass:LastClass!$A$2:$A$31),
MATCH(TRUE,MMULT((THREED(FirstClass:LastClass!$A$2:$A$31)<>TRANSPOSE(A$2:A2))
-(THREED(FirstClass:LastClass!$A$2:$A$31)=""),ROW(A$2:A31)^0)=ROWS(A$2:A31),0))

Select A3 and fill down until the formulas return #N/A.

B1:
Average Score

B2: <ARRAY FORMULA - press [Ctrl]+[Shift]+[Enter] to enter it.>
=AVERAGE(IF(THREED(FirstClass:LastClass!$A$1:$A$31)=A2,
THREED(FirstClass:LastClass!$B$1:$B$31)))

Select B2 and fill down to match the range of students in col A. This gives
average score by student. Then use the average scores in col B to rank the
students, and use VLOOKUP calls in the class worksheets to pull each student's
ranking overall from this created tables of rankings.

I suppose it'd be possible to write a VBA user-defined function that could do
this without requiring building a table in another worksheet, but it'd involve
such wasteful redundancy that I'm not going to go any further than stating that
it's a possibility. What you want to do is best done by summarizing *once* in a
*central* location, then referring to those centralized results as needed.