Want to find sum of Top 70% students' marks

sunilbsrv2k

Board Regular
Joined
May 25, 2018
Messages
64
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

New Member
Joined
Jun 18, 2019
Messages
16
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

Board Regular
Joined
May 25, 2018
Messages
64
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

Board Regular
Joined
May 25, 2018
Messages
64
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
 

Forum statistics

Threads
1,082,501
Messages
5,365,942
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top