Help require to give ranking

[mso3]

Hi friends,
At present there are 7 subjects for 3 divisions. The subjects and divisions may change as per the exam. Test, terminal test 2, annual. I want to sort then give ranks for each subject; give serial numbers for each subject according to the divisions as shown in the output sheet for reference. I want to run the macro on the ‘Data’ sheet. Here are 3 divisions so there are 3 serial numbers for each subjects. It may change as per the number of divisions. If there are 10 divisions then there will be 10 serial numbers for each subjects. I want to rank for each group of subject. Here are 3 divisions so 3 ranks; if 10 divisions then 10 ranks.

Any help will be appreciated.

Thanking you.

[StuCram]

Hello

The first problem is notice is that you have a column of scores similar to "18/20" or "35/40". Those cell are treated as text in Excel, not as numerical values. Therefore, you can not compare them as numbers.

The simpler solution is to enter the 'score' and the 'out of' values in separate columns and then have a third column to calculate the percent by dividing those two values.

Ranking the percents in each group is then fairly straight-forward; just be sure to have the correct range for each group.

The attached example shows these revisions to your sample spreadsheet.

Hope this helps,
- Stu

[FDibbins]

The simpler solution is to enter the 'score' and the 'out of' values in separate columns

Good suggestion Another altrenative would be to enter them ad decimals 0.9 or =18/20 etc, that way you still only use 1 column

[JBeaucaire]

1) you do need to split the marks out into two columns.
2) Once you've done that, a standard RANKIF trick in the form of a COUNTIFS can duplicate your results

= 1+COUNTIFS($I$4:$I$24,I4,$K$4:$K$24,">"&K4)

[mso3]

Hi JBeaucaire,
Please see the attachment and suggest me a amendment in the rank formula. It's very urgent because my work is stopped.

See 'English general and English special' subjects. I want o treat it as a single group of subjects and rank according to it from 1 to 6 and not 123 123.

Sorry for trouble.

Thanking you,

[JBeaucaire]

Let's assume that any string with the same first four letters is to be considered the "same"

English General
English Special

In N4 and coped down:

= 1+COUNTIFS($I$4:$I$24, LEFT(I4,4)&"*", $K$4:$K$24, ">"&K4)

[mso3]

Hi JBeaucaire,
Excellent! What a tricky solution.

Really I appreciate you for your solution beyond my imagination.

Thank you very much.