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 want to use excel to compare pairs of numbers from the "Pre/Post" column of the following data:

Student       Course          Pre/Post    Score
K300997203  FHS120100417    Pre         3
L286197217  FHS120100417    Pre         5
S106497203  FHS120100417    Pre         4
K300997203  FHS120100417    Post        4
L286197217  FHS120100417    Post        4
S106497203  FHS120100417    Post        4
S106497203  FHS220100424    Pre         4

Specifically, I want a cell to contain the difference of the value in the "Score" column where "Pre" and "Post" appear, respectively, in rows with the same value in the "Student" and "Course" columns.

For example, Student K300997203 has a row containing Course FHS120100417, a score of 3, and "Pre"

AND

Student K300997203 has a row containing Course FHS120100417, a score of 4, and "Post".

How can I calculate a cell value as the score in the row containing "Post" minus the score in the row containing "Pre"?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

This is a perfect place for a pivot table. (Instructions assume Excel 2007)

  • Select your data and then insert a Pivot Table
  • Add Course, Student and Pre/Post to the Row Labels in the order given
  • Add Score to the Values
  • Select any cell in the pivot table that contains 'Pre' or 'Post'
  • Go to Formulas -> Calculated Item...
  • Select Pre/Post from the Fields list
  • In the Name field type Delta
  • In the Formula field type =Post-Pre
  • Click OK - you'll now see the Delta values appear in your pivot data
  • Click the down arrow next to Row Labels and deselect Pre and Post from the list.
  • Adjust Total and Subtotals to your taste (I recommend turning them off in this case.)

(Thank you for asking this - you gave me an opportunity to learn the difference between calculated fields and calculated items in pivot tables :)

EDIT: I should add that if you convert your data to a List first, then you can easily append data to it and hit refresh on your pivot table to have the new data calculated and displayed.

share|improve this answer

With your data starting in A1, student number in A11 and Course in B11, use this formula

=SUMPRODUCT(($A$2:$A$8=A11)*($B$2:$B$8=B11)*($C$2:$C$8="Post")*($D$2:$D$8))-SUMPRODUCT(($A$2:$A$8=A11)*($B$2:$B$8=B11)*($C$2:$C$8="Pre")*($D$2:$D$8))
share|improve this answer

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.