Maintaining Course Grades in an Excel Gradebook Spreadsheet

1
Maintaining Course Grades in an Excel Gradebook
Spreadsheet

• Steve Bonham
• Last revised 9/11/2002

2
Overview-- We will learn

• Strategies for organizing digital gradebook
• Review Useful Excel Techniques for
• Freezing panes (rows columns)
• Formatting cells (bg color, text wrap, text
  rotation)
• Page set-up Printing
• Hiding (temporarily) Columns /or rows
• Commonly used formulas and functions
• Average - Sum - Min - Max
• Math Sentence Structure (PEMDAS)
• Using IF Statements
• Error prevention techniques
• Absolute Performers
• Color Coding Rows
• The Nine Commandments for Excel Gradebooks

3
Opening Your Practice File

• After Opening Excel
• Close your Book1 spreadsheet
• Select File Open Desktop Dropbox
  gradebook.xls

4
For this workshop the gradebook.xls file you have
opened is read only!

• So that you can make and save changes to this
  file first select File Save as
• Use your firstname_lastname for your filename
• For example mine will be saved as steve_bonham.xls

5
Entering and/or Editing Cell Data

• Select the cell (click it or navigate to it)
• Retype the data to replace it
• Lets fix Sandra Bullochs name in cell A5! Just
  click it retype it
• Ditto for Marilyn Monroe (in C14)

6
Adjusting Column Width

• Note that Barbara Streisands last name (and
  others too) is not completely displayed
• There are two ways to adjust column width
• Select column(s) by clicking or click-dragging
  column header(s) and either
• Drag intersection between headers) or
• Double-click an intersection (formats width of
  selected columns to be as wide as required to
  display all data)

7
More on Editing Cell Data

• Sometimes its easier to correct (instead of
  replace the data)!
• For instance check out cell A11- I think this is
  an error.
• Click the pointer in the edit box and correct the
  data (lets remove Gould and/or Brolin

8
Deleting column(s)

• We really dont need middle initials Lets
  delete that column
• Select column(s) to delete (click column B
  header)
• Select Edit Delete

9
Inserting column(s)

• Since your last gradebook update youve given
  Quiz 3 (the scores for which we will enter
  shortly) but also you given another Quiz (Quiz
  4). You need to insert a new column for Quiz 4!
• Select the column(s) where you want the new
  one(s) inserted (click the column H header)
• Then Select Insert columns

10
Inserting row(s)

• We have a new student to add (Thomas Cruise
  recently enrolled in your course)
• Click (or click-drag across) the row headers to
  select the position of the new row(s)
• Select Insert Rows
• Enter data for Thomas
• First last name, ID Code, cuts (4), Quiz 1 2
  scores(0)

11
Updating or Entering Data in a Range
Within a selected range, the white cell is where
the data will be inserted!

• Now we are ready to update our grades!
• Lets enter our students scores for quizzes 3 and
  4
• Select the range where data is to be entered
  G4H15
• Within a Range Tabs will auto-return at the end
  of a line (remember too that shift-tab moves
  selection to left)

12
Delete row (student drops)

• Jodi Foster has a new movie contract and is
  dropping our class!
• Select row(s) to delete (click row header)
• Select Edit Delete

13
Our First Formula (Average Function)

• Lets use the averaging function for the Quiz Avg
  column.
• Select cell I4 and enter average(E4H4)
• Formulas
• start with an equal sign
• DYNAMICALLY display a value based on input data
• This formula says put the average of Q1 to Q4
  here

14
Lets apply that formula to nearby cells

• Enter to see if the formula works
• Select the cell again
• Drag the anchor downward to apply the same
  averaging function to cells below

Before
After
15
Repeat this same process for the Exam average
column

• Start at cell M4
• Enter the averaging function
• Apply the function to the cells below
• You will get an error message because it is
  illegal to divide by zero! Excel wants some
  scores in the midterm and final columns!!
• So lets put some in!

16
Here are some scores to use

• But feel free to use your own!
• The formula seems to work

17
Input some scores for the paper

• Feel free to use your own

18
Ensure Accurate Formulas by using a minimal and
maximal performer

• Enter Absolutes for
• Minimal performance - Zzeke Zzero
• Maximal performance - Aamy Aawesom
• Format their rows for ease in identification

19
Apply the formulas used to the cells for Aamy
Zzekes QuizAvg, ExamAvg, and OverallSubTot areas

• Select the bottom-most cell where a formula is
  used and drag the anchor across the cells for
  Aamy and Zzeke.

20
Apply the formulas used to the cells for Aamy
Zzekes QuizAvg, ExamAvg, and OverallSubTot areas

• Afterwards you can clear the formula from cells
  in rows 15 and 16.

21
Apply the formulas used to the cells for Aamy
Zzekes QuizAvg, ExamAvg, and OverallSubTot areas

• Repeat this process for Columns M (ExamAvg) and N
  (OverallSub)

• AND YES!!! You can do both columns at once!

22
Math Sentence Basics

• Highlight cell where you want the result
• Enter formula
• Begin with equal sign ()
• Use addresses of cells to be used as inputs
• IMPORTANT! Enter formula in algebraic form
• Precedence of operations (), , /, -
• Please excuse my dear aunt Sally
• Example (B2C2)/2 results in 85 CORRECT!
• Example B2C2/2 results in 125!!
  INCORRECT!!!!
• Result will be dynamically updated
• To ensure avoiding errors in formulas use a
  maximal score row and a minimal score row

23
Now for an overall grade which requires writing a
math sentence

• The formula needs to say
• Add up the quiz average X .2 (20 of overall)
• the paper X .3 (30 of overall)
• and the Exam average X .5 (50 of overall)
• That formula is expressed in Excel lingo as
• (I40.2)(J40.3)(M40.5)
• NOTE that cell locations are used to identify
  Quiz Average and such!

24
Now apply this math sentence to the cells below

• How do you know if this formula is correct?
• You dont! But you will shortly

25
First though a useful Excel functionFreezing
Panes!

• Note that if we had many more assessment areas we
  could scroll right to enter data but we could no
  longer see student names
• Likewise if we have more than 40 students we can
  scroll down but we will no longer see the
  assement labels at the top of the sheet.
• How can this be improved?

• Select the cell below and right of the
  columns/rows to be frozen then
• Select Window Freeze Panes
• Now we can scroll down and still see the column
  header info. You can scroll right and still see
  names.

26
Using a Bonus via use of an IF statement

• The idea
• IF a student has less than 3 absences wed like
  to reward them by adding 1 point to the final
  average
• How to write this in Excel lingo?
• IF(D4
• Translated this means
• IF Cuts is less than 3 then
• put 1 here
• Else
• put 0 here

27
Understanding IF Statements

• ENGLISH
• IF cuts is less than 3 then
• Put 1 in Bonus column
• otherwise
• Put 0 in Bonus column

EXCELish IF(d4
28
Apply this IF statement downward to all rows (yes
even Aamy Zzeke)

• Then clean up rows 15 16

29
More on IF statementsIt is possible to Nest
statementsWhat if students are required to have
75 or greater on BOTH Final Exam and Overall to
pass?
EXCELish IF(L4")))
30
The Final Average (with Bonus included)

• Another simple math sentence
• Apply to cells below
• Clean up rows 15 16

31
Class Averages

• How do you know how the class is performing?
• Formulas can be written to average a number of
  values in a column

32
Class Averages

• Then apply this formula horizontally

33
Summary-- We learned

• To organize digital gradebook
• How to Freeze panes (rows columns)
• To Format cells (bg color, text wrap, text
  rotation)
• About Page set-up Printing Options
• To Temporarily Hide Columns /or Rows
• About Commonly used formulas and functions
• Average - Sum - Min - Max
• Math Sentence Structure (PEMDAS)
• Using IF Statements
• Error prevention techniques
• Absolute Performers
• Color Coding Rows
• The Nine Commandments for Excel Gradebooks

34
Nine Commandments for Excel Gradebooks

• Thou shalt
• not discard the traditional hardcopy gradebook
• organize your gradebook in advance and tie your
  formulas directly to your course syllabus
• use descriptive (but brief) column headers
• include Percentage Notes above Header row
• keep a backup of your digital gradebook in a
  different location
• use absolute extremes (Amy and Zeke) to
  ensure accurate formulas
• alternately color code rows in your gradebook.
• not sort a subset of thy data
• not post grades using SSI numbers as the student
  identifier