Saving Time (and Paper) with Automated Assessment using Excel – Three Rivers TESOL Fall Conference 2019

Hello, Three Rivers TESOL!

My presentation is about two spreadsheet templates I have developed to save me time in assessing student work.

Link to spreadsheets (ZIP file)

Automatic Grader and Error Analysis.xltx

Think of this spreadsheet like a Scantron/bubble-sheet grader. It enables you to very quickly score and generate reports on tests. It’s specialized for multiple choice and true/false questions, but it is compatible with other question types as well (see tips below).

What the instructor does:

  1. Enter the scoring key for the exam in row 4.
  2. Enter any subscore categories you want to calculate in row 39 (optional).
  3. Enter students’ names in column A (optional).
  4. Enter students’ answers across rows 5-24.

The spreadsheet calculates:

  • Whether any specific answer is right or wrong (color-coded green = correct, red = incorrect)
  • Each student’s raw score and percentage for the full exam
  • An error analysis of each item showing the percentage of students who answered each item correctly and breaking down which distractors were chosen the most often
  • A subscore breakdown (if you entered subscore categories in step 2 above)

Extra tips and notes:

  • When I look at the error analysis table (Table 2), I check for any questions with an accuracy rate of 50% or less. Then, I check to see if the errors are distributed randomly or if one distractor got the majority of responses. I use this information to help me decide if the question and/or distractors should be revised.
  • If some of your answers aren’t multiple-choice or true/false, no problem! You can type numbers and words into the key and into student answer cells, and the spreadsheet will still match them and count them for credit correctly.
  • For questions where answers may vary (such as short answer), I suggest putting them in the spreadsheet as T/F questions with the correct answer as T. If the student gave an acceptable answer, put T in the spreadsheet so it counts for credit. If a student gives a bad answer, record their answer as F so their score does not count for credit.
  • The spreadsheet assumes that every question is worth exactly one point. If you have a question worth two or more points, the spreadsheet will not calculate the correct final score. You can work around this by entering the question twice into the spreadsheet. (For example, if question 10 is worth 2 points, you can have two columns called “10”, each with the same key and the same student answers, to ensure it’s counted for a total of 2 points.)

Rubric to Email Grader.xltx

This spreadsheet is used for speaking and writing activities, i.e. performance assessments with multiple rubric categories. It vastly streamlines creating feedback reports, calculating grades, and returning everything to students.

What the instructor does:

  1. Enter the instructor’s own name (A1) and the name of the activity (B1).
  2. Enter the rubric categories (C1-H1) and how many points the categories are worth (C2-H2).
  3. Enter students’ names and email addresses (columns A and B).
  4. For each student, enter their subscores according to the rubric and type any additional comments you have (columns C-I).
  5. Click the “Create Email” link next to one of the students (column J)
  6. Copy and paste in your automatically-generated feedback email (column K)

What the spreadsheet does:

  • Performs all of the math for you to generate:
    • total raw score
    • total percentage
    • subscore percentages
  • Generates a simple feedback email, pre-addressed to the student and with subject line pre-filled in, which provides all of the student’s score information as well as your prose feedback

Extra tips and notes:

  • For the Additional Comments column, it’s a little clunky to write long paragraph-sized messages directly into a spreadsheet cell. For greater convenience, you may want to type your comments in a separate text editor (like Word, Notepad, or TextEdit) and then paste your paragraph into the cell when you’re done typing it.
  • I suggest also attaching the student’s original work (written work or audio recording of a speech) to the email.
  • It may also be helpful to attach a blank version of the “paper” rubric that you otherwise would have filled out by hand.
  • The cells in column K generate the email message via a very complicated formula which is easy to break, so if you want to add anything or change the wording, it’s safest to copy and paste the message somewhere else first, then make any necessary changes to the text.
October 14, 2019Permalink Leave a comment

ELI Computer Lab Class: Week 2 – Quizlet.com

Today’s post is about Quizlet!

Quizlet.com is a free website which helps learners to study. I’ve written about it previously.

Over the years, many Quizlet sets have been created by ELI teachers. See them below!

Not every Quizlet set listed below is relevant for every semester. For example, in one semester, we might teach the odd chapters of a book (1, 3, 5, etc), and in the next, we might teach the even chapters (2, 4, 6, etc). However, in the list below, you can often find all chapters: 1, 2, 3, 4, 5, 6, etc.

If you have questions about which Quizlet sets relate to your current ELI curriculum, you can ask your teacher.

Enjoy!

Quizlet Sets for Level 2

Core Vocabulary 2

Individual weeks

Groups of weeks

All words

Reading 2

Writing 2

Spelling Exercises:  http://www.aaaspell.com 

Grammar 2

Listening 2

Speaking 2

Quizlet Sets for Level 3

Core Vocabulary 3

Review Sets

Reading 3

Writing 3

Spelling Exercises:

Spelling, Definitions, Dictation, Sample Sentences: 

Grammar 3

Listening 3

 

Speaking 3

Quizlet Sets for Level 4

Core Vocabulary 4

Grammar 4

Reading 4

 

 

Writing 4

Spelling Exercises:

Spelling, Definitions, Dictation, Sample Sentences: 

Listening 4

Speaking 4

Evening course 4

Quizlet Sets for Level 5

Core Vocabulary 5

 

Reading 5

Writing 5

Grammar 5

Listening 5

Speaking 5

Quizlet Sets for Level 6

Core Vocabulary 6

Reading 6

Writing 6

Grammar 6

Listening 6

Speaking 6

 

 

 

January 23, 2019Permalink Leave a comment

ELI Computer Lab Class: Week 1 – Welcome! and Typing.com

Hello! Welcome to the ELI Computer Lab Class. Each week, I will share the materials and information from the ELI Computer Lab Class in this blog.

You can see the slides from the first week of class below. If the slides do not load in your browser, click here to access the slides.

Today’s resource is about typing.com!

An exercise on typing.com An exercise on typing.com

As its name suggests, typing.com is a website which helps you learn how to type properly on a US English keyboard. The website is free, and it offers a few dozen sequential tutorials that guide you from the most basic beginner-level practice (F, J, and Space) up to advanced-level full-keyboard speed and accuracy drills. Students can create accounts to track their progress and earn achievement badges.

Typing is an important skill for ESL/EFL students due to the amount of typed work they may be expected to produce in classes as well as their later careers. It’s also a necessary skill to enable students to confidently tackle standardized exams (such as the TOEFL and GRE) which require that students type original compositions under time pressure.

So check it out!

January 16, 2019Permalink Leave a comment