Rank based on criteria
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" wrote:
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
|