How do I calculate a departmental GPA? I’m using a Mac.

Here is a sample spreadsheet with the pivot table already set up. If desired, download it and paste in your own data.

 Overview

Departmental GPA or major GPA is really a special GPA. That is, it’s a GPA calculated using only the grade credits earned and hours earned in certain classes. For most, this means all the classes in a particular subject area (like psychology or journalism). For others, it’s more complicated. For example, International Studies majors calculate the major GPA based on certain classes that belong to many different subject areas.

Either way, it’s important that you clearly define what classes will count towards this special GPA before you begin.

Calculating the departmental or major GPA can be broken down into three overall steps.

Step 1: Download student grade data into a spreadsheet.

Step 2: Summarize the data and calculate the GPA using a pivot table.

Step 3: Limit the GPA calculation to the subject area or classes you want.

Step 1: Download student grade data into a spreadsheet.

1.  Click the Query Viewer link under CS Tools. The Query Viewer page appears.
2. Enter part of the query’s name (BU_SR_XSCRIPT_DATA) and click the Search button . A list of queries that match your search criteria will appear at the bottom. Any query that starts with BU_SR_XSCRIPT_DATA will work; pick the one which best fits the population you want to select.
3. Click the HTML link to run the query.
4. A new query window will open. Enter:
   1. Academic Institution: BUTLR
   2. Academic Plan or %: %xx% [where ‘xx’ are two letters to indicate the desired majors, minors and secondary majors]
   3. Current Term: [the current term code]
5. Click the View Results button, then click on the Excel Spreadsheet link.
6. Save the file to a convenient location by clicking Save in the ‘File Download’ window that opens. Then select a location and name the file. Click Save.

Step 2: Summarize the data and calculate the GPA using a pivot table in Excel 2007.

Note: Here is a sample spreadsheet with the pivot table already set up. If desired, download it and paste in your own data.

1. Sort the data and remove any rows you don’t want included in the GPA calculation.
2.  Click on the diamond in the upper left corner (above the number ‘1’ row label) to select all data.
3.  From the main toolbar, choose Data. From the submenu, select Pivot Table Report.
4.  The wizard finds your data. Click the Next button.
5.  On Step 2 of the wizard, click the Next button.
6.  On Step 3 of the wizard, click the Layout button.
7.  Drag the boxes on the right into the areas on the left as follows.
   1. Page: Acad Plan and Subject
   2. Row: Name
8. Double click on the Count of Grd Points box. Change Count to Sum and click OK.Drag the box into the Column area.
9. Double click on the Count of Attempted for Grade Credit box. Change Count to Sum and click OK. Drag the box into the Column area.
10. Click OK, then Finish.
11. Click inside the cell Sum of Attempted for Grade Credit. Click on the Insert menu and select Calculated Field.
12. In the Fields box, click Grd Points, then click Insert Field. In the formula field, enter a “/” and then insert the Attempted for Grade Credit field after that. The result should look like the following.=’Grd Points’/’Attempted for Grade Credit’
13. Click Add, then OK.
14. Click inside the first cell that contains “Sum of Field 1” and type GPA. Press the tab key to move out of the field and “GPA” will replace all the “Sum of Field 1” labels.
15. Save your spreadsheet.

Step 3: Limit the GPA calculation to the subject area or classes you want.

Here’s the fun part – use the pivot table’s features to limit which classes are used to calculate the GPA. Depending on your scenario, follow the steps below.

Scenario 1: If all the classes in only one subject area count, click the subject drop-down list in cell A1 at the top of the spreadsheet and choose the desired subject area code. The pivot table filters out all other subject areas and thus only calculates the GPA for the selected classes.

Scenario 2: Only certain classes count and the subject areas vary: click the drop-down list for the “Subject Area & Catalog Number” column and uncheck the boxes next to the classes that don’t count toward the special GPA. Then click OK. For example, if SP 102 and 103 did not count towards this special GPA, you can only check the boxes next to the classes that do count thereby calculating a GPA based on a set of classes that you determine.