|
Rank based on criteria
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 |
Rank based on criteria
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 On Mar 31, 9:58*am, 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 |
Rank based on criteria
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" wrote: 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 On Mar 31, 9:58 am, 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 |
Rank based on criteria
Okay, forget that.
How many different sections are you likely to have, i.e. 1A, 1B, 1C etc? Pete On Mar 31, 11:34*am, yshridhar wrote: 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" wrote: 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 On Mar 31, 9:58 am, 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- Hide quoted text - - Show quoted text - |
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 |
Rank based on criteria
There are 10 classes. Each class has three sections (A, B and C).
I am using Xl03. regards Sreedhar "Pete_UK" wrote: Okay, forget that. How many different sections are you likely to have, i.e. 1A, 1B, 1C etc? Pete On Mar 31, 11:34 am, yshridhar wrote: 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" wrote: 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 On Mar 31, 9:58 am, 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- Hide quoted text - - Show quoted text - |
Rank based on criteria
Thank alot Lori, It works. I have to scan your formula for the frequency.
Regards Sreedhar "Lori" wrote: 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 |
| All times are GMT +1. The time now is 12:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com