Formula for Ranking

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would also like to have a formula that would go into the "Grade Point
Average" column to give me the top student – then formula for the 2nd top
student, then the 3rd, on down to ten. Thanks
 
One way ..

Assuming names in col A, grades (numbers) in col B, data from row2 down

Put in C2:
=IF(B2="","",B2-ROW()/10^10)
Leave C1 blank

Put in D2:
=IF(ROW(A1)>COUNT($C:$C),"",INDEX(A:A,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0)))

Copy D2 to E2. Select C2:E2, copy down to the last row of source data. Hide
away col C. Cols D & E will return a full descending sort of the names &
grades, inclusive cases with tied scores, if any. Tied scores' cases will be
returned in the same relative order that they appear within cols A and B.
Just read-off the top xx students as desired (or copy and paste special as
values elsewhere).
 
Max, I can't quite get this to work. My list is a little more complex and
Some of the other elements may be getting in the way. Colum C and D I have
last and first names. E is an ID #, F is a date, G-R are class corurses with
a grade next to the course, in S I have the GPS's which is an average array
of the grades excluding 0. Your formula as I adjusted it gives me a ranking
of the GPA in decending order, but some names and grades are listed several
times. I do have some empty rows at the bottom of the list and I think that
scatters some 0.00 in my ranking. So I have messed up some where: Here is
your formula as I adjusted it.

=IF(ROW(C5)>COUNT($S:$S),"",INDEX(C:C,MATCH(LARGE($S:$S,ROW(C5)),$S:$S,0)))

C5 is an empty row. A is a hidden cloume for a VLOOKUP, and B are my row
number for each student.
Thanks again for you help. Neil H
 
Max, to add to what I wrote earlier, Tied scores are returned in the
relitive order as you stated, however name on first tied score is repeated in
all the tied scores. Oh and the 0.00 in the name in the ranking was comeing
from a sum of averages I have at the bottom of the list. So I moved the sum
to a different cell.
Thanks
 
Here's a slightly revised set up & criteria col to suit ..

An illustrative sample file is available at:
http://www.savefile.com/files/531492
AutoList_in_Desc_Order_by_GPA.xls

Source setup assumptions: First & Last Names in cols C & D, ID#s in col E,
GPAs calculated in col S, with data/calcs in row2 down

Assuming 6 empty cols to the right of col S
Place labels in V1:Y1 : Fname, Lname, ID#, GPA
(presume we want to return only the 4 cols above from the source cols)

In T2:
=IF(OR(S2="",ROUND(S2,2)=0),"",S2-ROW()/10^10)
Leave T1 blank. This is the revised criteria col.

In U2: =IF(V2="","",ROW(A1))
This creates the row numbering col for the auto listing

In V2:
=IF(ROW(A1)>COUNT($T:$T),"",INDEX(C:C,MATCH(LARGE($T:$T,ROW(A1)),$T:$T,0)))
Copy V2 to X2. This is to return the First & Last Names and the ID# cols.

In Y2:
=IF(ROW(A1)>COUNT($T:$T),"",INDEX(S:S,MATCH(LARGE($T:$T,ROW(A1)),$T:$T,0)))
This is to return the GPA col.

Select T2:Y2, copy down to the max expected extent of source data. Hide away
col T. Cols V to Y will return a full descending sort of the First & Last
names, ID#s & GPAs, inclusive cases with tied scores, if any. Tied scores'
cases will be returned in the same relative order that they appear within the
source cols. Just read-off the top xx students as desired (or copy and paste
special as values elsewhere).
 
Thanks Max, Outstanding. Great results. Just what I was looking for, and
you made it ez to do.
 
Hello Max,
I used your setup below and it work out for me, however, on the last to
cells of the formula it has blanks. Could you tell me how to fix this? Thanks

Results

33.99999999 ICT 1
32.99999999 RFD 1
2.999999991 YQR 1
845
53.99999999
 
It should work fine. Perhaps you might have adapted it incorrectly to suit.
For example, if your source data in cols A and B starts in row4 down, then
this should be the set-up

In C4:
=IF(B4="","",B4-ROW()/10^10)
Ensure C1:C3 are left blank

In D4:
=IF(ROW(A1)>COUNT($C:$C),"",INDEX(A:A,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0)))
Copy D4 to E4. Select C4:E4, copy down to the last row of source data. Cols
D and E returns the desired results.

Give it another try ..
 
Ok, it did not seem to like not having anthing above the empty cells, so I
just move everthing up to cell "1".

=IF(IP1="","",IP1-ROW()/10^10)

Stange, que no?

Aslo, could you explane the formulas?


Thanks for the help.
 
=IF(IP1="","",IP1-ROW()/10^10)

That's the tiebreaker criteria col which flags the relative positions of
lines to be returned via producing a sequential series of unique numbers for
use in:
MATCH(LARGE($IP:$IP,ROW(A1)),$IP:$IP,0)

The refrain to leave cells above it blank, should the criteria col start in
a row other than row1 is because any numbers placed in cells above might
disrupt the sequential numbers within the criteria col which would then
throw the MATCH out-of-sync.

If you use "uglier, longer" specific range references in the INDEX/MATCH,
eg:

MATCH(LARGE($IP$4:$IP$5000,ROW(A1)),$IP$4:$IP$5000,0)

instead of the neater entire col references ($IP:$IP), then the above
precaution would not arise. A simple trade-off.
 
Back
Top