Lesson Title: Learning to Calculate Grades in Microsoft Excel

Introduction: After learning about different types of assessments, students will learn and practice how to enter grades of student work into an Excel spreadsheet and calculate grades, percentages, weighted assignments, and projections. Even though there are software programs available to most teachers, it is essential that students learning to become educators understand the basic concepts behind grades and calculating them for students. This lesson will allow students to practice with real student data and understand the concepts behind weighted grades and will provide the opportunity for students to build the skills necessary to keep track of their own grades if they wish. Students will think about and develop their own grading policy and design their spreadsheet to reflect their own hypothetical classroom grading procedures. Calculating grades is an example of how technology has made teachers’ grading tasks easier, so students will also be learning about this as a way that technology influences education and provides opportunities for making teaching and learning more efficient and effective.

Grade or Age Level of Student(s): grades 10-12, Orientation to Teaching

Objectives:

  • Students will be able to properly enter and calculate grade data from student assignments/tests/projects by using a Microsoft Excel spreadsheet and the proper tools/functions/formulas.

  • Students will be able to analyze options for weighting different types of assessments by determining what type they feel should be most emphasized.

  • Students will be able to project student grades at the end of the semester and/or how many more points the student will need to earn in order to receive a passing grade by using certain functions in Microsoft Excel.

  • Students will be able to explain the benefits of using technology for calculating student grades.

Standards Addressed: Orientation to Teaching I, Unit 3: Learning to Teach; 3.5 Identify various assessment techniques, 3.5.1: Develop assessment techniques for a lesson and/or unit, 3.7: Explain the effective use of technology in the classroom

Timeline: This lesson should take approximately two 50-minute class periods, depending on if the grades and data have been previously obtained.

Materials: Computers, Microsoft Excel template for calculating grades, previously obtained data set from mentor teachers, handout for determining grading policy and grading scale

Grouping Strategies: Students will work individually on this task so each student will get direct experience with Excel and learning through spreadsheets. My classroom is a computer lab, so there are plenty of computer stations for each student to have his or her own. If not enough computers are available, students could work in pairs, but it is preferred that each student have access to his or her own computer. Some work on this project could also be done outside of school if students have access to computers at home or elsewhere.

Learning Activities: Good afternoon class! Today we will be working with Microsoft Excel and learning to calculate grades, like real teachers. How many of you have experience working with Excel? (Wait for student response and discuss their level of proficiency with Excel) Even if you are not familiar with Excel at all, you should still be able to do this lesson without any problem. So you’ll know what is expected of you, these are our objectives for this lesson. You will: (read objectives aloud or write them on the board so students are aware of the content to be learned)

  • be able to properly enter and calculate grade data from student assignments/tests/projects by using a Microsoft Excel spreadsheet and the proper tools/functions/formulas.

  • be able to analyze options for weighting different types of assessments by determining what type they feel should be most emphasized.

  • be able to project student grades at the end of the semester and/or how many more points the student will need to earn in order to receive a passing grade by using certain functions in Microsoft Excel.

  • be able to explain the benefits of using technology for calculating student grades.

Let’s begin by opening up or pulling out your documents containing the grade data from the elementary students. Remember, the grades for each student have been randomized, so they are not accurate per student. You will still be using your elementary students’ names for this project, but I want you to remember that their names and this information must be kept confidential, even though it is not accurate. (Wait for students to locate and open the files containing the data—these could be Word, Excel, or other files, or students could have hard copies)

Whatever format your information is in, you will be inserting this data into an Excel spreadsheet to analyze it and calculate student grades. First, we must go over some important information before you can effectively complete this assignment. Remember when we talked about types of assessments and different ways to evaluate student learning? You learned about how to create assessments, as well as why each type of assessment is important. Today, you will be dividing the assessment examples you received from your mentor teacher into three categories: classwork/homework, projects, and exams. You should have at least a few grades for each example. Your first task is to assign a weighted value to each of these three categories. For example, in this class, your classwork is 35% of your total grade, projects are 50%, and exams are 15% of your total grade. So which category of learning assessments is most weighted? Once you determine your weighted scale, you will be creating a pie chart in Excel to illustrate your weight policy. (Walk students through creating a pie chart in Excel, using their own weighted scale.)

That’s right! So based on what you have learned about assessments, you will decide how much to weight each category. This is completely up to you—and you won’t necessarily have the same as your neighbor’s, but that is ok.

Next, you will determine what your grading scale will be. The traditional grading scale is 90-100 is an A, 80-90 is a B, and so forth. Do you have any courses in which this is not the case? If so, did your teacher explain this grading scale to you and why it is used? You will determine the grading scale for your hypothetical class. You don’t have to choose the traditional method, but whatever scale you come up with, you will need to justify it with a thorough explanation, just like the weighted scale you determine. I’m going to give you several minutes to do both of these tasks. Please develop your grading policy and your grading scale and include detailed rationales for both. You will do this on the handout I am passing out to you. (pass out handout for grading policy and grading scale—give students at least 10 minutes to do this task)

Now that you have decided your grading scale and assessment weight, this will guide the rest of your project. Please open the Excel file I sent you. (Email/share the Excel file with the grade calculating template with the students so they can access it at school and at home if necessary) You should see this (show students with your computer and the projector what they should see when they open the document). Let’s begin by looking at what the document contains (at this point, go over each component of the Excel document, explaining to the students what they will do in each cell). Before you enter any information about the students, you will enter your grading scale information and grade weight policy information several rows down on your spreadsheet. This will remind you what you decided and help you in the next several steps. (Demonstrate for students on the projector.)

Explaining the Excel document: First, look at the column headings. You will see a column for student name, then the three assessment categories (classwork, projects, and exams), and then columns labeled average, grade, points earned, points possible, total points for the course, and then a last column divided into three sections that are labeled “For an A, B, or C.” Taking the data you received from your teacher, you will begin entering the information, including student names, from left to right. You will need to insert more rows to accommodate all the students you have information on. Begin with the student name, and then decide which of the assessments are classwork, which ones are projects, and which ones are exams. You may need to insert more columns under each category, as there are only two for each in this template. Enter the assessment name and the percentage received on it. For example, look at Jane—she received a 99% on her Paint by Number assignment, but she got a 69% on her Unit 1 Exam. You will do this for all the students from your mentor teacher.

Then, depending on the weight scale you chose for your hypothetical class, you will have to change the formula in column H. When you click on column H, see how it says =(((B4+C4)/2)*0.15)+(((D4+E4)/2*0.55))+(((F4+G4)/2)*0.3)? This is the formula already inserted for you. However, you will have to change it slightly. (go over instructions for changing the formula to accurately depict their grading policy) Use this to help you:

=(((B4+C4)/2)*0.15)+(((D4+E4)/2*0.55))+(((F4+G4)/2)*0.3)

Cells under each category (various assessments)

Number of assessments under each category

Percentage of weight for each category

Once you have inserted all grades and checked to make sure your information is accurate, assign each student a letter grade based on the grading scale you determined. You will do this manually. (Give the students a few minutes to do this—more explanation may be required.) Great job students! Now let’s move on to the points section. You should see three columns regarding points. One says points earned, another points possible, and then total course points. The number of points the students have earned so far in the course should be located in the information your mentor teacher gave you—you will simply type this number into the points earned column for each student. Next, enter the total number of points possible for each student so far this semester. This number should be the same for all students. Lastly, the total course points column is the total number of points possible in the entire course/year. This should be much larger than the points possible/points earned columns. This will also be the same number for each student. You can enter the first couple rows and then highlight those two rows and click the corner of the cell and drag all the way down the column. This should save you time. (Demonstrate for the students.)

The last section is for you to help your students (and perhaps their parents) understand their progress in the class. Teachers should be able to project for students how they may progress in order to receive a passing grade. This helps students know where they stand and how hard they need to work for the rest of the class to meet their goal. Remember when we talked about goal setting? Ok, let’s take a look at these columns. You can add more columns if you want, but I just stuck with A, B, and C. These are what I want my students to aim for. Once you fill out these columns, you will know how many more points each student will need to earn these letter grades. If you’ll click on each of these cells, you will see the formula that I entered for each column. You will be changing these formulas based on your grading scale, if you chose one different from mine and based on the number of total points in the course. In this example, there are 1,000 points possible in the course, so I did a mathematical calculation to determine what 90% of 1,000 was, what 80% was, and so forth. You will do the same for the total number of points for your class. (Demonstrate an example for students on the board—they should each calculate how to figure percentage of a number).

(Give students several more minutes to complete the last three columns of their spreadsheet and to finalize their projects. They can color-code or personalize them in their own way. Review with students about why each stage of this spreadsheet is important.)

Ok, class, let’s talk about how this technological tool has made the lives of teachers easier when calculating grades. How many years ago do you think teachers had to calculate all grades by hand? What other tools are out there now for teachers to use? (Hold a class discussion about how technology has changed drastically in the past half-century and how teachers today perform tasks differently than teachers several decades ago.) For your final activity, I’d like for you to get out your journals and write a one-page reflection of this lesson. At a minimum, please respond to the following questions in complete sentences, using correct spelling and grammar:

(put these questions on the projector or write them on the board):

  1. How familiar were you with Microsoft Excel before beginning this project?

  2. What did you learn about Excel and its capabilities that you didn’t already know?

  3. How will you use Excel in the future for schoolwork or otherwise?

  4. Think of a problem or assignment you have had in the past that you could have solved with Excel or spreadsheet software. What would you have done differently?

  5. Why is technology important in education? For teachers? For students?

Thank you for participating in class and this activity—I really hope all of you know a bit more about Excel than you did before and you are confident in your abilities to calculate grades for students. This is an essential skill that teachers must have, and now you have it! Congratulations, and I look forward to viewing your spreadsheets. Please make sure you saved your spreadsheets as your first and last name to the shared drive so I can view them. Have a great day!

Diversity: This lesson could be adapted in many ways to meet the needs of diverse learners. For example, instead of real data, the teacher could make up his or her own data sets and assign them to the students. More thorough or detailed templates could be provided to students who need more structure. Assistive technologies could also be utilized to aid students with diverse learning needs or impairments. For example, text readers or voice recognition software could be used to help students use the computer.

Assessment: The assessment for this lesson will involve certain requirements for participation, feasibility of grading scale/policy, accuracy, technology literacy, and written explanation. This project is aligned with all six of the International Society for Technology in Education’s NETS standards for students:

  1. Creativity and Innovation: This project motivates students to think like teachers and put themselves in a teacher’s position when it comes to grades and assessment of student progress.

  2. Communication and Collaboration: Even though this is preferably an individual task, students will collaborate with each other previous to this assignment to discuss various assessment methods and which ones are more appropriate for different situations. Students will also be required to collaborate with their elementary students and mentor teachers during their internship and to obtain the grade information necessary to complete this assignment.

  3. Research and Information Fluency: Students are applying their knowledge of Microsoft Excel to organize, analyze, and use information. They will use this technological tool to determine projections of student grades as well as to understand student progress.

  4. Critical Thinking, Problem Solving, and Decision Making: Students will need to think critically and make decisions about their grading policies to determine how to appropriately assess their students’ learning. Even though this is a hypothetical situation, students should learn from this experience and can carry these concepts and skills through to when they become teachers.

  5. Digital Citizenship: With these skills in Excel, students may be ahead of their peers and be able to serve as leaders who can promote digital literacy and citizenship. Students will be under and ethical agreement about keeping students’ grade information confidential and not disclosing any information about the elementary students or teachers to individuals outside the school setting.

  6. Technology Operations and Concepts: Even though this assignment is fairly low in technological literacy, students will be learning about and practicing skills they will use for the rest of their lives. They are making the connection between education and technology and will be asked to explain how this technology is important to teachers and students.

References:

http://www.internet4classrooms.com/excel_grade.htm

http://www.iste.org/docs/pdfs/nets-s-standards.pdf?sfvrsn=2