I have a large spreadsheet in Excel 2013 with student records. Each row corresponds to one student registered in one course. The spreadsheet spans 5 years of student records. I am trying to create a pivot table that shows me the distinct count of students who have 6 or more courses as well as those with fewer than 6 courses.
One row has the following fields (and many more):
Student
Number
Academic Year
Course ID
Calculated Field (as
above)
The pivot table will count unique student courses (ie. John Doe in Course A). I have a calculated field in my main data that combines Academic Year (ex. 2015), student number (ex. 345987) and Course ID (ex. 195100) into a field like AY2015SN345987CS195100. So, if student 345987 takes 7 different courses in 2015, I want that to count as 7. Then I create my pivot table with rows: Academic Year and Student Number; Values are Distinct Count of Calculated Field
I have created a pivot table that calculates all distinct student courses into something like this:
Year # of Students
+2015 501
+2014 640
+2013 465
...
If I expand my pivot table a bit more to individual student number rows, it looks like this:
Year # of Students
2015 501
345987 7
123765 5
...
I can also create a value filter (i.e. distinct count of courses is greater than or equal to 6) applied to the Student Number, so I meet one of my criteria (ex. 6 or more) into something like this:
Year 6 or More
2015 356
2014 458
2013 290
I can also filter and get those with less than 6 courses.
However, what I really want is to show the distinct count of those students that have 6 or more courses in one year and the distinct count of those students that have less than 6 courses into a single pivot table.
The final product would look something like one of these:
Year 6 or More Less than 6
2015 356 145
2014 458 182
2013 290 175
Best Answer
Data summarization with greater than and less than – Excel-Formula & PivotTable
Assuming the DATA is located at range
B6:D176with the following fields as described by the user (adjust range as required):Student : Student Number
Year : Academic Year
Course ID
Key : Calculated Field
Objectives :
Classify within each year in the database the student population in two groups:
a. Students with 6 or more courses
b. Students with less than 6 courses
I’m not sure that all calculations needed can be performed by a
PivotTable, therefore I propose to use working fields to do the calculations then aPivotTableto summarize the results.Working Fields :
Key : Let’s take out of this calculation the
Course Idin order to have a field that contains theYear\Studentcombination. Enter this formula inE7then copy till last recordAY.SN.CS.Cnt : Count of
Year\Student\Course. Enter this formula inF7then copy till last recordAY.SN.Cnt : Count of
Year\Student. Enter this formula inG7then copy till last recordAY.SN.CS >= 6 : Quantity of
recordsforYear\Studentwith 6 or more courses. Enter this formula inH7then copy till last recordAY.SN.CS < 6 : Quantity of
recordsforYear\Studentwith less than 6 courses. Enter this formula inI7then copy till last recordAY.SN >= 6 : Quantity of
Studentswith 6 or more courses in a Year. Enter this formula inJ7then copy till last recordAY.SN < 6 : Quantity of
Studentswith less than 6 courses in a Year. Enter this formula inK7then copy till last recordFig. 1
The working fields can be hidden if it’s preferable to the user
Then create a PivotTable as per figure below
Fig. 2
The PivotTable reads that in Year 2015 there are:
Studentswith 6 or more coursesAY.SN >= 6and a total of 22 coursesAY.SN.CS >= 6Studentswith less than 6 coursesAY.SN < 6and a total of 10 coursesAY.SN.CS < 6