Top 3 within a group with an average

[SeaQuest]

Here is a challenging wrinkle to a problem I posted earlier. I have a
table of student ID's (numeric) courses and marks such as shown in
this example:

ID Course Mark
123456 Math 11 80
123456 English 11 82
123456 Socials 11 75
123456 Science 11 92
123456 Geog 11 84
123456 Physics 11 70
234567 Math 12 65
234567 English 12 71
234567 Chem 12 60
234567 Geog 12 88
234567 Phys 12 70


I want to produce a list that shows the top 3 courses for each student
as well as an average of those top 3 courses. Example


ID Course Mark Avg
123456 Science 11 92
123456 Geog 11 84
123456 English 11 82 86
234567 Geog 12 88
234567 English 12 71
234567 Phys 12 70 76


In other words I basically want to sort first by student ID then by
mark and filter out all but the top 3 marks for each student and
calculate an average. I am not having much success with this and am
wondering if someone can assist
me?


Jim

 

[MacRae]

do an average select requery in your select line

although it would look like

 

[Roger Govier]

Hi

You could do it with a Pivot Table. Post back with which version of
Excel you are using and I will give the appropriate instructions.

 

[Pete_UK]

I suggested this in answer to your earlier posting:

" ...

Sort your data set by ID (ascending order) and by Mark (descending
order), then put this formula in D2 (assuming row 1 is for headings):

=IF(A2=A1,D1+1,1)

Copy this formula down column D - you might like to put the heading
"Rank" in D1.

Then apply autofilter on column D - choose "Custom" then "Less than"
and 4, and you will have the top three scoring subjects for each
student ID. If you wish, you could highlight the visible rows of
data,
click <copy> then move the cursor to a blank area (or even another
sheet) and paste the data in - only the filtered data will be copied
across.

.... "

though I don't know if you have read that as there is no indication.

However, if you do the above, then all you need to do is enter this
formula in E4 after sorting:

=IF(D4=3,AVERAGE(C2:C4),"")

then copy this down column E as required.

Hope this helps.

Pete