Rank function

[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

 

[Bernie Deitrick]

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

 

[Guest]

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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

 

[Harlan Grove]

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

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.

 

[Guest]

Thank you very much Mr. Bernie. This UDF works for my data. My sincere
thanks to you
regards
Sridhar

 

[Guest]

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

 

[Bernie Deitrick]

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

 

[Guest]

Thank Mr. Bernie for the Code. It works well
Regards
Sridhar