Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm probably missing something very obvious, but I have the following data:

ACCT 4
ACCT 2
CS   20
CS   10
ENG  20
ENG  10
ENG  30

What I want is to get the average of each department. So it should look like the following:

ACCT 3
CS   15
ENG  20

I have about 4000 rows of data and don't want to have to split the data into 40+ sheets. Thanks!

share|improve this question
1  
Alternatively, I could do this all through bash scripting. But it would be nice to know how to do it in Excel –  tifkin Oct 28 '12 at 23:49
add comment

1 Answer

up vote 0 down vote accepted

A pivot table will do this for you:

enter image description here

Make sure your columns have headings.

  1. Go to Insert / PivotTable
  2. Accept the defaults on the first page for the data source
  3. Select the range containing the course and score
  4. Drag the Course column from the list presented to the columns field
  5. Drag the Score column from the list presented to the data field
  6. You will then be given a pivot table with totals, so change the dropdown in the top left to "Average of Score"

I'll add additional images if this isn't easy to follow.

share|improve this answer
    
Ah, Pivot Tables are what I was looking for. I didn't quite understand your directions, but I found this youtube video quite helpful: youtube.com/watch?v=l0a0dCgFA5g –  tifkin Oct 29 '12 at 1:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.