Conditional Lookup in Excel
- by Keyslinger
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"?