Want to find sum of Top 70% students' marks

[sunilbsrv2k]

Hi All,

I have an unusual yet important question.

My question is: I obtained the marks of about 150 students, in 4 different subjects.

I have them in the list with 3 columns; first column is "Subject", 2nd is Student ID and 3rd is respective marks in the particular subject.

Issue here is I need to find the sum of top 70% students in each subject. For example if I have marks of 100 students in the subject "Mathematics", then I should find the sum of the marks of Top 70 students.

Could you please kindly help me in this

Thanks

 

[RossTattersall]

The Rank formula is your friend here.

Add a column with the formula RANK([students mark],[range where marks are]) and drag it down. Then you can SUMIF off of this formula.

 

[sunilbsrv2k]

The Rank formula is your friend here.

Add a column with the formula RANK([students mark],[range where marks are]) and drag it down. Then you can SUMIF off of this formula.

Hi Thanks for the answer.

However, my question is not just 70% of the Top ranks; but its should be for each subject.

 

[sunilbsrv2k]

Hi All,

I have found an answer. To accomplish this, we need to use :

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=SUMPRODUCT(LARGE((G8:G1000 = BN8)*(AE8:AE1000),{1,2,3}))[/FONT]

However, now the issue is , I want to replace {1,2,3} with larger numbers like 1 to 50 etc.

Is this possible?

Thanks