Part #1: Download and Work with the Data

Open the data in Excel and Explore the Data

Using excel on your location computer, apply what you learned in class to clean and aggregate

the data into more useful and meaningful information.

Tips: Excel is a very powerful tool that can be a bit overwhelming. For this assignment, make

use of column filters, SUM, AVERAGE, and SUBTOTALS functions.

1. In Excel, put your cursor focus on the first cell for the Student List* table.

2. In the "Data" tab, click the "Filter" button.

3.

After clicking the "Filter" button, you should see the header row change to now contain

arrows to indicate dropdown filters.

Full Student List with Attendance and Midterm Grades

Student (Last Name, First Nam Attendan Mid Term GraLate Submissi

Lee, Eli

24

13

Brown, Jaxon

14.2

Young, Kimiko

13.7

n.a

26

By using the dropdown filters, you can gain more insight into the data by isolating a single

collection of data.

Section

TTh 4-6 PM

Online

TTh 4-6 PM

4) To find the Average Attendance for a specific section, first add a SUBTOTAL row to the

bottom of the table.

The syntax for the SUBTOTAL function in Excel is -

SUBTOTAL(function_num,ref1,ref2,...)/nIncludes

hidden

values

1

2

3

4

5

6

7

8

9

10

11

Ignores

hidden

values

101

102

103

104

105

106

107

108

109

110

111

Function

AVERAGE

COUNT

COUNTA

MAX

MIN

PRODUCT

STDEV

STDEVP

SUM

VAR

VARP/nAt 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,C5:C64)

14.2

14

14.9

IMF 1-3 PM

MF 1-3 PM

MF 1-3 PM

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" -

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./nAt 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,C5:C64)

14.2

14

14.9

IMF 1-3 PM

MF 1-3 PM

MF 1-3 PM

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" -

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./nYou 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.

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

14.9

MF 1

=SUBTOTAL(3, E5: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)".