Formula for Ranking

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neil H - 27 Feb 2007 01:26 GMT

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

Reply to this Message

Max - 27 Feb 2007 09:04 GMT

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).

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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

Reply to this Message

Neil H - 03 Mar 2007 17:34 GMT

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

> One way ..
>
[quoted text clipped - 16 lines]
> > Average" column to give me the top student – then formula for the 2nd top
> > student, then the 3rd, on down to ten. Thanks

Reply to this Message

Neil H - 03 Mar 2007 18:24 GMT

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

> One way ..
>
[quoted text clipped - 16 lines]
> > Average" column to give me the top student – then formula for the 2nd top
> > student, then the 3rd, on down to ten. Thanks

Reply to this Message

Max - 04 Mar 2007 09:05 GMT

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).

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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.

> 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
[quoted text clipped - 10 lines]
> C5 is an empty row. A is a hidden column for a VLOOKUP, and B are my row
> number for each student.

Reply to this Message

Neil H - 04 Mar 2007 22:29 GMT

Thanks Max, Outstanding. Great results. Just what I was looking for, and
you made it ez to do.

> Here's a slightly revised set up & criteria col to suit ..
>
[quoted text clipped - 50 lines]
> > C5 is an empty row. A is a hidden column for a VLOOKUP, and B are my row
> > number for each student.

Reply to this Message

Max - 04 Mar 2007 23:30 GMT

Always good to hear that, Neil !
You're welcome, thanks for the feedback

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks Max, Outstanding. Great results.
> Just what I was looking for, and
> you made it ez to do.

Reply to this Message

pgarcia - 11 Dec 2007 21:04 GMT

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

> One way ..
>
[quoted text clipped - 16 lines]
> > Average" column to give me the top student – then formula for the 2nd top
> > student, then the 3rd, on down to ten. Thanks

Reply to this Message

Max - 12 Dec 2007 08:22 GMT

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 ..

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hello Max,
> I used your setup below and it work out for me, however, on the last to
[quoted text clipped - 8 lines]
> 845
> 53.99999999

Reply to this Message

pgarcia - 12 Dec 2007 18:41 GMT

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.

> 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
[quoted text clipped - 22 lines]
> > 845
> > 53.99999999

Reply to this Message

Max - 13 Dec 2007 01:53 GMT

> =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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Ok, it did not seem to like not having anthing above the empty cells, so I
> just move everthing up to cell "1".
[quoted text clipped - 4 lines]
>
> Aslo, could you explane the formulas?

Reply to this Message