Rank for different Criterion Groups

[amkkhan]

Is it possible to get different set of rank for different criterion groups in the same column.
Actually I wish to get each exams rank to be compared with one of its own kind for each student.
For example rank of 1st-Term should be compared with results of 1st-Term of all students.
In the same way the rank of Mid-Term should be compared with results of Mid-Term of all students.
Same for rest of exams and

rank of Grand-Total should be compared with results of Grand-Total of all students.

And essentially the rank that a student is achieving after final term in "Grand Total" row.
My apologies XL2BB is not working at the moment. If you are coming with solution please also post XL2BB

 

[Marcelo Branco]

Maybe...

K9 copied down
=COUNTIFS($G$9:$G$28,G9,$H$9:$H$28,">"&H9)+1

Hope this helps

M.

 

[amkkhan]

Maybe...

K9 copied down
=COUNTIFS($G$9:$G$28,G9,$H$9:$H$28,">"&H9)+1

Hope this helps

M.

Thanks @Marcelo Branco but I am looking for Rank formula in Column K

 

[Toadstool]

Hi Amkkhan,

The merged cells add a wrinkle.

Amkkhan.xlsx
	E	F	G	H	I	J	K
8	Roll No.	Name	Exam	Obt. Marks	T Marks	%age	Rank
9	1	Joe	1st Term	470	500	94	1
10			Mid Term	380	500	76	3
11			2nd Term	410	500	82	2
12			Final Term	450	500	90	2
13		Grand Total		1710	2000	85.5	1
14	2	Nick	1st Term	320	500	64	3
15			Mid Term	290	500	58	4
16			2nd Term	345	500	69	3
17			Final Term	495	500	99	1
18		Grand Total		1450	2000	72.5	2
19	3	Sunny	1st Term	400	500	80	2
20			Mid Term	490	500	98	1
21			2nd Term	300	500	60	4
22			Final Term	100	500	20	4
23		Grand Total		1290	2000	64.5	3
24	4	James	1st Term	260	500	52	4
25			Mid Term	390	500	78	2
26			2nd Term	480	500	96	1
27			Final Term	156	500	31.2	3
28		Grand Total		1286	2000	64.3	4
Sheet1

Cell Formulas
Range	Formula
J9:J28	J9	=H9/I9*100
K9:K28	K9	=IF($F9="Grand Total",COUNTIFS($F$9:$F$9999,$F9,$J$9:$J$9999,">"&J9)+1,COUNTIFS($G$9:$G$9999,$G9,$H$9:$H$9999,">"&$H9)+1)
H28:I28,H23:I23,H18:I18,H13:I13	H13	=SUM(H9:H12)
E14,E24,E19	E14	=E9+1

 

[Marcelo Branco]

Thanks @Marcelo Branco but I am looking for Rank formula in Column K

I suggested a formula in K9 copied down, as requested

By the way, unmerge cells F:G on the rows that contain Grand Total. Type Grand Total only in column G

M.

 

[Marcelo Branco]

Try

Pasta1
	F	G	H	I	J	K
8	Name	Exam	Obt Marks	T. Marks	% age	Rank
9	Joe	1st Term	470	500	94,0%	1
10		2nd Term	380	500	76,0%	3
11		3rd Term	410	500	82,0%	2
12		Final Term	450	500	90,0%	2
13		Grand Total	1710	2000	85,5%	1
14	Nick	1st Term	320	500	64,0%	3
15		2nd Term	290	500	58,0%	4
16		3rd Term	345	500	69,0%	3
17		Final Term	495	500	99,0%	1
18		Grand Total	1450	2000	72,5%	2
19	Sunny	1st Term	400	500	80,0%	2
20		2nd Term	490	500	98,0%	1
21		3rd Term	300	500	60,0%	4
22		Final Term	100	500	20,0%	4
23		Grand Total	1290	2000	64,5%	3
24	James	1st Term	260	500	52,0%	4
25		2nd Term	390	500	78,0%	2
26		3rd Term	480	500	96,0%	1
27		Final Term	156	500	31,2%	3
28		Grand Total	1286	2000	64,3%	4
Plan3

Cell Formulas
Range	Formula
J9:J28	J9	=H9/I9
K9:K28	K9	=COUNTIFS($G$9:$G$28,G9,$H$9:$H$28,">"&H9)+1
H28:I28,H23:I23,H18:I18,H13:I13	H13	=SUM(H9:H12)

M.

 

[amkkhan]

I suggested a formula in K9 copied down, as requested

By the way, unmerge cells F:G on the rows that contain Grand Total. Type Grand Total only in column G

M.

Thanks @Marcelo Branco
What is benefit of it. actually "Grand Total" is representing a difference between all above 4 exam terms

 

[amkkhan]

Thanks @Marcelo Branco and @Toadstool

If you don't mind, May I try your solutions later cuz now I am out of home and can't apply it while using my Android phone

 

[Marcelo Branco]

What is benefit of it. actually

In your case, the benefit would be to use a simpler formula for all rows. You might notice that Toadstool had to use an IF condition to handle the merged cells. Unless cells are not used in any formula, as a general rule, avoid merged cells!

M.

 

[amkkhan]

Every time I tried to get XL2BB for my sheet, my laptop got stuck because perhaps my Excel sheet contain too much content. I applied both the formulas of @Marcelo Branco and @Toadstool but it didn't worked. So I can only show you the real situation by this image.
I needed Ranks in Right most(very last Column).