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


"Bernie Deitrick" wrote:

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

"yshridhar" wrote in message
...
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" wrote:

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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



"yshridhar" wrote in message
...
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" wrote:

Sridhar,

=SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000B2))+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


"yshridhar" wrote in message
...
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