At the bottom of the table, add the equation for SUBTOTAL with the AVERAGE sub function

and select all data in the Attendance column.

Collins, Willow

Scott, Hunter

Harris, David

Totals

22

26

22

-SUBTOTAL(1,CS:C64)

14.2

14

14.9

Based on the section you filter the table with, your SUBTOTAL will automatically update with the

average attendance for that section.

5) For ease of understanding, update the precision for the SUBTOTAL cells to 2 decimal

places. Right click on the cell, select "Format Cell".

In the dialog box select Category as "Number" and Decimal Places as "2" -

IMF 1-3 PM

MF 1-3 PM

MF 1-3 PM

6) Some students in the MF and TTh sections have not attended any classes and they have a

NULL or Non-Number value listed in the attendance column. NULL and Non-Number values

will not be calculated in the SUBTOTAL Average.

You must include those students in your averages to get the correct average, so to make sure

they are included into your SUBTOTAL, enter 0 into those cells.

14.9

This is an important data cleansing step.

7) Perform the same techniques in "5)" and "6)" to clean and calculate the Average Midterm

Score for each section.

8) To calculate the Total Late Midterms, use the SUBTOTAL function with a sub function of

COUNTA

MF 1-

-SUBTOTAL(3,ES:E64)

9) Start filling out the template table. The final column to be calculated is the Average Midterm

Grade (%). To calculate the percentage, divide the average score for each section with the

total possible score and select the column category type as "Percentage". The total possible

score is 10. The category type can be selected from the same "Format Cells" dialog window as

detailed in *5)".