Learning Sheets Functions for Oral Assessment Grades (or, why my PLN rocks!)

Learning Sheets Functions for Oral Assessment Grades (or, why my PLN rocks!)

I recently put a plea out to my PLN to help me figure out how to collect grades from a form for oral assessment.

Click here for the original form. Click here for the study sheet for my students.

My idea is, from the 27 questions, I will randomly ask 10 questions to one student at a time. I will check off my assessment of their success on each answer (on my iPad).

Thanks to Kim Pollishuke, Pascal Morissette, Scott Monahan, Diana Mancuso and Jenny Gray for helping me find a solution.

Here are the steps:

1. I used a grid question with questions in the row and to numbers 1, 2, 3, 4 in the columns. I then inserted as an image (just above) Faible, Acceptable, Bien, Excellent.

2. I went to the live form and filled it out once as a test student.  

3. In the associated sheet I added two columns: Average and Grade (insert, column right).

4. In the Average column, I clicked on the first cell (Row 2), then clicked on the functions button, then clicked on “Average”:

5. I highlighted all the cells to the l of this column, stopping at the cell after the student’s name then clicked “Enter”. This gave me an average out of 4.

6. I then copied this “If” function formula that the brilliant Diana Mancuso made for me:

=IF(C6<1.5,"D",IF(C6<1.7,"D+",IF(C6<2,"C-",IF(C6<2.4,"C",IF(C6<2.8,"C+",IF(C6<3,"B-",IF(C6<3.1,"B",IF(C6<3.2,"B+",IF(C6<3.35,"A-",IF(C6<3.7,"A",IF(C6<4.1,"A+")))))))))))

(Based on the following grades out of 4):

A+ = 3.8 - 4

A   = 3.4 - 3.7

A-  = 3.2 - 3.3

B+ = 3.1

B  = 3

B- = 2.8 - 2.9

C+ = 2.5 - 2.7

C  = 2 - 2.4

C- = 1.8 - 1.9

D+ = 1.5 - 1.7

D  = 1 - 1.4

and pasted it in the first cell under the “Grades” column. Just click out of the cell after. This gave me a letter grade for the first student.

(NOTE: The “AD” in the formula refers to the letters of the “Average” column that I added. You will need to change this to the letter of the column that your average column falls under.)

7. For subsequent students, it’s just a matter of dragging down the little blue square in the bottom right hand corner to cover the new cells (in “Average” and then “Grades” columns) and you will see the magic happen.

THIS, MY FRIENDS, IS WHY MY PLN ROCKS!!!!

Syl

PS. In grade 5, students have to ask the question as well as answer it. Click here for the study sheet, click here for the form. When assessing, I will simply give them the Key Word (Mot Clé) and they will have to give me the question and the answer.