Technology Upgrade: Build a Customized Grade Book

Practically, enhanced teaching begins when teachers themselves are comfortable using technology and applying it to real-world problems. Before there can be a “technology upgrade” in the classroom, teachers must understand the benefits and uses of a particular piece of technology so they can share those with their students. And what better place for teachers to begin getting comfortable with technology than with their own personal productivity?

Technology in schools is no “passing fad.” But neither is every new tech initiative a “silver bullet” to solve every problem faced by teachers and students. Too often, technology and related skills are either taken for granted or overlooked. Schools invest money in technology and expect teachers to use it to educate and empower 21st century students. But before we can have empowered students, a solid foundation must be built on reliable infrastructure, effective administration, extensive resources, and enhanced teaching.

The first half of this presentation will focus on the first three points:

  1. Reliable infrastructure
  2. Effective administration
  3. Extensive resources

The second half will focus primarily on enhanced teaching.

Practically, enhanced teaching begins when teachers themselves are comfortable using technology and applying it to real-world problems. Before there can be a “technology upgrade” in the classroom, teachers must understand the benefits and uses of a particular piece of technology so they can share those with their students. And what better place for teachers to begin getting comfortable with technology than with their own personal productivity?

Therefore, the conclusion of this presentation will give suggestions for enhanced personal productivity as well as lay out the basic steps for the creation of a very versatile class grade book in Microsoft Excel.

KOTESOL Grade book Presentation Notes

Chameleons and camouflage: Do you stand out? Or do you blend in (and hide)?

My self intro: I’ve taught 6 years in Korea, 3 at Avalon Academy, 3 at Jeonju University. I have a BS in Computer Science and MFA in Media Design – earned completely online. I’ve taught Teacher Training classes in Technology at JJU for 2 semesters, summer 2011 and spring 2012 (now).

I’ve worked with educational websites throughout my entire career, including Avalon’s online writing and speaking platform, the MOODLE CMS for my own JJU classes, and my own experience taking a year of online class for my Master’s degree from Full Sail University in Florida. So, I have a pretty good idea what good teaching with technology looks like.

Additionally, when I teach about making grade books in Excel, it consistently is ranked as the best and most popular skill I teach other teachers. So, I’ll talk about that in this presentation.

The book I’m currently teaching is “The Classroom Teacher’s Technology Survival Guide” by Doug Johnson, an English teacher, librarian, and district technology manager in his state in the US. It’s a great book, and many of my ideas have come from it.

Part One

Let me start with an observation – Technology Revolution. It’s revolutionized every field in the world…except maybe, education. In fact, ONLY education has not changed so much that a teacher from 1900 would not know what to do in today’s classrooms.

And when it comes to tech changes, we are creatures of habit – we like the old stuff even if its outdated. For example, new interfaces from Gmail and Facebook (Timeline) and the tons of resistance those received from (some) users. Sir Ken Robinson in a TED talk mentioned kids and wristwatches.

For kids under the age of 20, they see no need to wear a wristwatch, because technology tells the time all around them. But for the older generation (teachers), most of us wear wristwatches because we are creatures of habit, and its how we grew up. For our generation, a wristwatch was the best (only?) way to tell time, and even though it isn’t the most effective means of telling time nowadays, we still wear them.

Tech in schools is no “passing fad.” Neither is tech in schools a “silver bullet” (to solve every single problem with a single technology – though many administrators may come back from conferences with that kind of notion).

So then, WHY do we want tech in schools?

Automate/Informate

Tech in general really has 2 main functions:

  1. Automate – take standard operations and make them faster, more accurate, and less labor intensive.
  2. Informate (coined by Shoshana Zuboff in her book “In the Age of the Smart Machine” (1988)) – translating descriptions and measurements of activities, events, and objects into information.

The real power is in “informate.”

Examples:

  • Grade book: Automate (calculate grades), Informate (make available to students, inform teachers of trends, inform parents)
  • Website tutorials: Automate (lessons), Informate (learn new skills at my own pace)
  • Student Devices: Automate (homework), Informate (learn from anywhere)

So how can we go from 1906 to 2012? “Attitude is everything.”

Look at the “Survival Skills for your Own Tech Use.” We’ll look at #5 in the following section.

Hierarchies

  • Maslow’s Hierarchy of Needs
  • Johnson’s Hierarchy of Educational Technology Needs
  • Johnson’s Hierarchy of Student Technology Use

We will look at “Personally Productive, Enhanced Teaching” with the grade book.

Part 2

There are probably 3 kinds of (Excel) people watching my talk today:

  1. Those who don’t really know Excel (a blank spreadsheet)
  2. Those who know the basics (a basic grade book)
  3. Power-users (like me) who want advanced techniques

This look at Excel with be fairly basic, but even Power-users can benefit from some of the specialization I will talk about, AND the fact that we are all forgetful. This will be a good reminder to those who already know how, and a good beginning for those who wish to learn more.

Creating the Grade book

The grade book has spaces for:

  1. Student names and numbers
  2. Test and Assignment grades
  3. Participation and Attendance grades
  4. Final grade percentages
  5. Final letter grades
  6. A schedule of classes and topics
  7. A place for Bonus Points
  8. A place for calculating a grading curve

The grade book needs to have the following requirements met:

  1. Use the =SUM(...) function
  2. Use the =IF(...) function
  3. Use 2 more functions of your choice:
    VLOOKUP, AVERAGE, COUNTIF, SUMIF,
    MAX, MIN, SMALL
  4. Fill out the info (including grades) for 10 students
  5. Make it “pretty” with good formatting and design

Example Gradebooks

  1. Simple Elementary Grade book
  2. Aaron’s Grade book Example

Example formulas:

=RANK(A1, A$1:A$10, 0)
Means RANK(“what student?”, “Whole class”, Descending order)

=IF(A1>=0.9, “A”, IF(A1>=0.8, “B”, IF(A1>=0.7, “C”, IF(A1>=0.6, “D”, “F”))))
Please remember to change the CELL numbers.

Hints:

  • FIX the view: 보기 – 틀고정
  • Data Validation: 데이터 유효성

Some notes:

  • ISBLANK(CELL) only works on cells that are truly blank (no formula)
  • Need to be sure that “Automatic Calculations” (not Manual) is selected in the Excel Options.
  • Do you want to use points or percentages?
  • You CAN find out how many Absences they have using COUNTIF – but that only works on a continuous range of cells, not multiple selections
  • You CAN assign an “F/A” grade if the student has 9 or more absences – but that must be the FIRST argument in the cell.
  • Need to use the test “>=” to test grades, if using only “>” (for example, > 90) then it will evaluate wrongly. 90 would be “B” but “91” would be “A”
  • Therefore, separating Attendance and Participation VERTICALLY, rather than horizontally would probably work better.

Resources