• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

a formula to pick 7 highest marks in a list

D

dawmah

New Member
Pls help with ths: you have a class table with students exam results. Subjects are nine and you need to pick only top 7.

after which you find th final average of those 7 best subjects. This will vary from student to student as each will not pass the same way. That's why a formula would be ideal. I hope it is clea.
 
You will use the Large() function to return the top 7 values

Then use the Index/Match combination to return the associated values
 
Here ya go.

=INDEX($A$4:$A$85,MATCH(LARGE($B$4:$B$85,ROWS($BF$1:BF1)),$B$4:$B$85,0))


Change the "$A$4:$A$85" range to the range of your students. then change the "$B$4:$B$85" to the range of your exam results. After that just drag the formula down. Leave "$BF$1:BF1" range alone.
 
Thank you so much Montrey, Hui 4 ur respnse. I wil test and let u knw if successful but i believe it wil work.

I ve bin away and couldn't respond earlier.
 
[pre]
Code: 
Column1	Maths	Eng	History	Scie	Sets	RE	AVERAGE
Mary	23	12	54	43	67	88	(this is where
Jose	33	34	12	24	55	66	the average should be,
Annah	44	44	87	54	63	45	which is made of top 4 marks)
[/pre]

please help with that calculation
 
Hi ,


Try this :


=AVERAGE(LARGE(B2:G2,{1,2,3,4}))


where B2:G2 is one row of marks for one student. Copy this down for the other students.


Narayan
 
Thank u!marvelous. One more, assuming i want to have a row per student of those top 4 marks with the specific subject heading on top just as it is above. How do i get that.
 
Hi ,


If we assume that your subjects are in row 1 , in the same order as in the data range , then you can use the following formula :


=IFERROR(IF(MATCH(B2,LARGE($B2:$G2,{1,2,3,4}),0)>0,B2),"")


Copy it across and down.


Narayan
 
Hi. Thanx a million again. You re a lifesaver Narayan.

Now i m goin to work on the students reports with only the top marks listed.

You have helped heaps.
 
Hi. Thanx a million again. You re a lifesaver Narayan.

Now i m goin to work on the students reports with only the top marks listed.

You have helped heaps.
 
You must log in or register to reply here.
Back
Top