Rank with duplicates values not overriding the next value

The following formula gives the rank for different subjects of a class.

=COUNTIFS(Subjects,C3,Marks,">"&D3)+1

However if 2 students score the same marks in say English subject, then the next Rank is calculated as 4. What I want is to modify this in a manner that the next Rank calculated be 3 instead of 4 and so on.

So the following table shows the values of RANK that I want generated.

--------------------------------------
Roll No | Subject | Marks | RANK |
--------------------------------------
-- 1 -- | English | ---80--| ---1---|
-- 2 -- | English | ---78--| ---2---|
-- 3 -- | English | ---78--| ---2---|
-- 4 -- | English | ---76--| ---3---|
-- 5 -- | English | ---75--| ---4---|
-- 6 -- | English | ---72--| ---5---|
..
..

-- 1 -- | Biology | ---92--| ---1---|
-- 2 -- | Biology | ---92--| ---1---|
-- 3 -- | Biology | ---90--| ---2---|
-- 4 -- | Biology | ---88--| ---3---|
-- 5 -- | Biology | ---88--| ---3---|
-- 6 -- | Biology | ---85--| ---4---|

Any help on this is appreciated. Thanks !

@Ajoo,

the problem with your desired ranking is that it looks like student 6 came 5th in the class in English, and 4th in the class in Biology.

Pete

Hi all !

Thanks for the answer and the insights.

@Kokosec : If the list is large and contiguous with no separation between the subjects ( unlike as shown in the table by me ), the formula fails at the change of subject. I would want to write the formula and pull it down across hundreds of rows and it should work across subject boundries.

@FlameRetired : That's not how I would want my result as shown by the yellow columns. I have showed the result I want in my table. Or am i missing / overlooking something in my answer.

the problem with your desired ranking is that it looks like student 6 came 5th in the class in English, and 4th in the class in Biology.

@Pete : I am not sure I understand what the problem is. If we go by the marks, then those are the ranks of student 6 as they should be.

Kokosec's reply seems to accomplish what I want but fails at the subject boundries. Any help in improving that would be great !

Thanks again to all of you!

If Kokosec's works for you except when change subjects and no spaces, you can try this:
=IF(B2<>B1,1,IF(C2<>C1,D1+1,D1))

Or try:

=SUMPRODUCT(($B$2:$B$20=B2)*($C$2:$C$20>C2)/COUNTIFS($B$2:$B$20,$B$2:$B$20,$C$2:$C$20,$C$2:$C$20))+1

Hi,

Grateful to receive so many replies !

@Gregb11: This does not seem to work. I tried with a lot of variations but could not get it working.

@kvsrinivasamurthy : Your formula is, I guess, only for one subject and works great there. However my problem is slightly more general and I needed it for all the subjects.

@menem : I cannot download your file. I tried thrice but each time i go to a page which says you are not logged in.

@Phuocam : Your formula works just great !! Thanks !! However, there is one more small issue that I would like to request modification for. What if there are some students absent and their result shows "A", then how can that be incorporated in your formula to give the correct result.

Thanks !

This formula works for me.

=IF(B2="","",IF(B2<>B1,1,IF(C2<>C1,D1+1,D1)))

I've uploaded a file that shows it working. Again, this was assuming that Kokosec's formula worked as you stated, meaning that the list would be sorted by Subject and then by Mark/Grade.

This is one of the problems when you paste in data as opposed to uploading a file. In your first post, you had rows where there were spaces. Now you don't. And now you've introduced an "A" under Marks where there were none before. What does the "A" mean, that they got a grade of A (91 - 100) or they are active? If column C = "A", then what do you want done - just bypass it? Can it be anything else than an "A" or a number - can it be "B", "C", etc.? You need to attach an actual Excel file and demonstrate clearly what you are looking for and what the problem is.

Hi Gregb11,

Thanks for the response. The "A" stands for Absent and will be nothing else, like B or C. So only A or numeric values in the Marks column.
In the earlier table , the spaces and .. between subjects was simply meant to depict more such data in the table. The exact table as is I have pasted in my immediately previous reply. Only there can be more subjects and so more such rows. Since the table runs into 100's of lines I thought it best to keep it small and limited.

So please consider the table referred to above as the actual data table. Thanks !

Originally Posted by Ajoo

What if there are some students absent and their result shows "A",
then how can that be incorporated in your formula to give the correct result.

Edit the formula:

=IF(ISNUMBER(C2),SUM(IF(($B$2:$B$20=B2)*($C$2:$C$20>C2)*ISNUMBER($C$2:$C$20),
1/COUNTIFS($B$2:$B$20,B2,$C$2:$C$20,$C$2:$C$20)))+1,"")

Enter with Ctrl+Shift+ Enter.

Hi !

@Phuocam : Thanks ! worked perfectly !! I really wish you can give some explanation of the working of this wonderful expression. I have no clue how this logic works, even though i have tried to wrack my brains a bit over it.

@Gregb11: I think your solution is the simplest if this last bit can be incorporated. I would very much love to see if you can do this by modifying the simple formula that you have applied.

Thanks all for the incredible response !!!

Regards

In D2, try:
=IF(C2="A","",IF(C1="A",1,IF(B2<>B1,1,IF(C2<>C1,D1+1,D1))))

Again, this assumes that all the subjects will be sorted, and then sorted by Marks with the "A"s at the top. If these assumptions aren't true, then this formula will not work.

Hi Gregb11,

Thanks for the reply and the new formula. I tried it on the same data that I put here but it seems to fail. Please find attached the file in case I have done something wrong or missed something.
Thanks again.

In COL C, your "A"s were not "A", the first one had a space after it, and the others had a couple of spaces after the A and also a space before the "A". When all spaces were removed, it seems to be working correctly.
If you're going to have spaces around the "A", I added "Trim" to the formula to take care of that. It's:
=IF(TRIM(C2)="A","",IF(TRIM(C1)="A",1,IF(B2<>B1,1,IF(C2<>C1,D1+1,D1))))
I've attache the file for your reference.

Hi Gregb11,

It's working perfectly. I am sorry that there were spaces around the "A" which were causing it to fail. Trim is a good idea. Thanks loads !

Thanks again to all for their replies and support.