Rank based on criteria

[yshridhar]

Hi all
The following is the data of school marks.
Sec No Marks Rank
1A 1A1 150 1
1A 1A10 147 3
1A 1A11 145 4
1A 1A12 147 3
1A 1A13 143 5
1A 1A14 127 6
1A 1A3 150 1
1A 1A4 149 2
1B 1B1 131 2
1B 1B10 127 4
1B 1B11 124 6
1B 1B12 134 1
1B 1B13 127 4
1B 1B14 125 5
1B 1B15 130 3
1C 1C1 136 1
1C 1C10 100 5
1C 1C11 111 2
1C 1C12 84 7
1C 1C13 105 4
1C 1C14 107 3
1C 1C15 98 6

I want the rank calculated on "sec". the rank above is calculated using
UDF (from excel community). Can it be done using functions?
Thank you all in advance.
Regards
Sreedhar

 

[Pete_UK]

If you use a helper column with this formula:

=A2&TEXT(C2,"000")

and copy this down, you can then use this column as the basis of your
ranking.

Hope this helps.

Pete

 

[yshridhar]

I could not get you Pete. What i want is a formula that calculates the rank
that result as in column-D. My data contains nearly 1000 entries and
differenet classes and sections.
regards
Sreedhar

 

[Pete_UK]

Okay, forget that.

How many different sections are you likely to have, i.e. 1A, 1B, 1C
etc?

Pete

 

[Lori]

maybe this, ctrl+shift+entered and filled down from row 2.

=SUMPRODUCT(--(IF(FREQUENCY(IF(Sec=A2,Marks),IF(Sec=A2,Marks,0)),Marks,0)>=C2))

 

[yshridhar]

There are 10 classes. Each class has three sections (A, B and C).
I am using Xl03.
regards
Sreedhar

 

[yshridhar]

Thank alot Lori, It works. I have to scan your formula for the frequency.
Regards
Sreedhar