Rank function

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

Guest

Hello everybody
We maintain school marks database in excel. Students belongs to different
calsses from 1 to 10th and different sections (say 1a, 1b, 2a, 2b etc). The
student id no is the primary kery of the database. The id no consists of
class-section and No (1A10). I want a function that ranks the students on
their class-section (say in 1A - first class A-section the ranks of each
student). Likewise for all the sections. The database is sorted on ID no.
For 10th class "X" is chosen for class number.
With regards
Sridhar
 
Sridhar,

=SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000>B2))+1

where column A has section, and column B has the score score. Put this in row 2, then copy down to
match your database.

HTH,
Bernie
MS Excel MVP
 
Thank you Mr. Bernie. I am sorry to say that it had some problem. I am
giving the data. I changed the formula instead of column B - column C
Section ID No Marks Rank-formula Actual Rank
1A 1A1 150 1 1
1A 1A3 150 1 1
1A 1A2 149 3 2
1A 1A24 149 3 2
1A 1A4 149 3 2
1A 1A17 148 6 3
1A 1A20 148 6 3
1A 1A5 148 6 3
1A 1A10 147 9 4
1A 1A12 147 9 4
1A 1A21 147 9 4
1A 1A16 146 12 5
1A 1A22 146 12 5
1A 1A6 146 12 5
1A 1A11 145 15 6
1A 1A23 145 15 6
1A 1A19 144 17 7
1A 1A25 144 17 7
1A 1A13 143 19 8
1A 1A18 143 19 8
1A 1A8 136 21 9
1A 1A14 127 22 10
1A 1A26 127 22 10
1A 1A9 102 24 11
1A 1A15 0 25 0
1A 1A7 0 25 0

Regards
Sridhar
 
Sridhar,

In your case, you could use a UDF (code below). Copy the code into a module in your workbook, then
use it like

=URank($C$2:$C$100,C2,$A$2:$A$100,A2)

Then copy down to match your table, where your data table is as you posted.

HTH,
Bernie
MS Excel MVP

Function URank(rScores As Range, _
rScore As Range, _
rSections As Range, _
rSection As Range) As Integer

Dim myUScores() As Double
Dim i As Integer
Dim iFirst As Integer
Dim iPos As Integer

iFirst = Application.Match(rSection.Value, rSections, False)

ReDim myUScores(1 To Application.CountIf(rSections, rSection.Value))
URank = 1

myUScores(1) = rScores(iFirst).Value
iPos = 2
For i = iFirst + 1 To rSections.Cells.Count
If rSections(i).Value = rSection.Value Then
If IsError(Application.Match(rScores(i).Value, myUScores, False)) Then
myUScores(iPos) = rScores(i).Value
If rScores(i).Value > rScore.Value Then URank = URank + 1
iPos = iPos + 1
End If
End If
Next i

End Function
 
I messed up. Use this version:

Function URank(rScores As Range, _
rScore As Range, _
rSections As Range, _
rSection As Range) As Integer

Dim myUScores() As Double
Dim i As Integer
Dim iFirst As Integer
Dim iPos As Integer

iFirst = Application.Match(rSection.Value, rSections, False)

ReDim myUScores(1 To Application.CountIf(rSections, rSection.Value))
URank = 1

iPos = 1
For i = iFirst To rSections.Cells.Count
If rSections(i).Value = rSection.Value Then
If IsError(Application.Match(rScores(i).Value, myUScores, False)) Then
myUScores(iPos) = rScores(i).Value
iPos = iPos + 1
If rScores(i).Value > rScore.Value Then
URank = URank + 1
End If
End If
End If
Next i

End Function

Sorry about that....


HTH,
Bernie
MS Excel MVP
 
yshridhar said:
Thank you Mr. Bernie. I am sorry to say that it had some problem. I am
giving the data. I changed the formula instead of column B - column C
reformatted
Section__ID No__Marks__Rank-formula__Actual Rank
1A_______1A1____150________1____________1
1A_______1A3____150________1____________1
1A_______1A2____149________3____________2
1A_______1A24___149________3____________2
1A_______1A4____149________3____________2
1A_______1A17___148________6____________3
1A_______1A20___148________6____________3
1A_______1A5____148________6____________3
1A_______1A10___147________9____________4
1A_______1A12___147________9____________4
1A_______1A21___147________9____________4
1A_______1A16___146_______12____________5
1A_______1A22___146_______12____________5
1A_______1A6____146_______12____________5
1A_______1A11___145_______15____________6
1A_______1A23___145_______15____________6
1A_______1A19___144_______17____________7
1A_______1A25___144_______17____________7
1A_______1A13___143_______19____________8
1A_______1A18___143_______19____________8
1A_______1A8____136_______21____________9
1A_______1A14___127_______22___________10
1A_______1A26___127_______22___________10
1A_______1A9____102_______24___________11
1A_______1A15_____0_______25____________0
1A_______1A7______0_______25____________0
Click to expand...

If this table were sorted by Section in whatever order then by Marks in
descending order, and this table spanned columns A to E with column headings
in row 1 and the first row of student records in row 2, the rank formulas
could be simplified to

D2:
=IF(C2=0,0,IF(A2<>A1,1,IF(C2=C1,D1,D1+1)))

Fill down as needed. If this table would be sorted only by section but
within section student records could be in any order, you'd need to use

D2:
=IF(C2=0,0,SUMPRODUCT((INDEX(C$2:C$1001,MATCH(A2,A$2:A$1001,0))
:INDEX(C$2:C$1001,MATCH(A2,A$2:A$1001,0)+COUNTIF(A$2:A$1001,A2)-1)>C2)
/(COUNTIF(INDEX(C$2:C$1001,MATCH(A2,A$2:A$1001,0)):INDEX(C$2:C$1001,
MATCH(A2,A$2:A$1001,0)+COUNTIF(A$2:A$1001,A2)-1),INDEX(C$2:C$1001,
MATCH(A2,A$2:A$1001,0)):INDEX(C$2:C$1001,MATCH(A2,A$2:A$1001,0)
+COUNTIF(A$2:A$1001,A2)-1))+(INDEX(C$2:C$1001,MATCH(A2,A$2:A$1001,0))
:INDEX(C$2:C$1001,MATCH(A2,A$2:A$1001,0)+COUNTIF(A$2:A$1001,A2)-1)<=C2)))+1)

Modify the range addresses for your actual table.

You'd be much better off sorting the table by section AND by marks and using
the simpler formula.
 
Thank you very much Mr. Bernie. This UDF works for my data. My sincere
thanks to you
regards
Sridhar
 
Sorry Mr. Bernie to trouble you once again. I need a little alteration in
the UDF. If the total is zero, then the rank is zero or blank
Sridhar
 
Either use

=IF(C2=0,"",URank(.....))

or change the code to

Function URank(rScores As Range, _
rScore As Range, _
rSections As Range, _
rSection As Range) As Integer

Dim myUScores() As Double
Dim i As Integer
Dim iFirst As Integer
Dim iPos As Integer

If rScore.Value = 0 Or rScore.Value = "" Then
URank = 0
Exit Function
End If

iFirst = Application.Match(rSection.Value, rSections, False)

ReDim myUScores(1 To Application.CountIf(rSections, rSection.Value))
URank = 1

iPos = 1
For i = iFirst To rSections.Cells.Count
If rSections(i).Value = rSection.Value Then
If IsError(Application.Match(rScores(i).Value, myUScores, False)) Then
myUScores(iPos) = rScores(i).Value
iPos = iPos + 1
If rScores(i).Value > rScore.Value Then
URank = URank + 1
End If
End If
End If
Next i

End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to finding student Rank and How to upload bulk marks of each student at a time. 1
Rank based on criteria 6
Picking the list from data 2
Max/Min function help 7
Count function without double counting 1
MS Access (School database design) 14
formula problem - UK national curriculum levels 10
Ranking students marks 0

Back
Top