Top 3 within a group with an average

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SeaQuest - 07 Jun 2007 02:41 GMT

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

Reply to this Message

MacRae - 07 Jun 2007 04:16 GMT

do an average select requery in your select line

although it would look like

> ID Course Mark Avg
> 123456 Science 11 92 86
[quoted text clipped - 3 lines]
> 234567 English 12 71 76
> 234567 Phys 12 70 76

> 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
[quoted text clipped - 31 lines]
>
> Jim

Reply to this Message

Roger Govier - 07 Jun 2007 11:47 GMT

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.

Signature

Regards

Roger Govier

> 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
[quoted text clipped - 31 lines]
>
> Jim

Reply to this Message

Pete_UK - 07 Jun 2007 12:18 GMT

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

> 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
[quoted text clipped - 31 lines]
>
> Jim

Reply to this Message