Join Aaron Quigley for an in-depth discussion in this video Writing the grade calculation functions, part of Office for Educators.
It's now time to write one of the most important functions for our grade book, the function that's going to calculate each students grade. I'm using the Grade Book Data file located in chapter six of the Exercise Files. This grade book is the exact same from last chapter, except for I've gone through and prepopulated some student names, I've added some assignments, and I've also added some dummy grades that we can use to calculate our individual student grades. If you'd like to add this data to the grade book you're creating, all this information is available in the lists dot. Excel xs file, which is also located in chapter six of the Exercise Folders.
Let's go ahead and dive right in to creating the grade function. We are missing one thing, however, and that is a few assignments and some assignment totals. I can't calculate a student score unless I know how many total points there are in the class. Let's jump back to our list one more time. We're going to copy over, are assignments and their points. So here I have the assignment values populated as well as some student's grades. I'm now ready go ahead and start calculating out student's grades. To do this we're going to write a formula. If I click inside this box I can actually go up and put my cursor inside the formula bar.
And actually write the formula up here. This will allow me to use some of the built in features such as inserting functions and function validation that are built right into Excel. For this function, we need to have a sum of all of the student grades divided by a sum of all the points possible in this workbook. The easiest way to do that is to literally have two summations that are divided. Using the equal sign, I'm going to start writing a function. This function is going to be a summation, which can be indicated by S U M. I'm going to put an open bracket to indicate what it is that I would like to sum up.
Instead of continuing to type the function at this point, I'm going to highlight the cells that I would like to sum. This is Jeff's grade, and i'd like to go ahead and highlight Jeff's grade in anything that happens in this assignment all the way to the end of the workbook. You can see in the upper box, it's actually gone ahead and put in an E4:AH4. I'm going to go ahead and hit enter. I've now written a function in this box that gives me the sum of the total points that Jeff has earned. What I need to do next is go ahead and take that sum function and I'm going to divide it by using a forward slash bar another sum.
I'm going to go ahead and write sum, I'm going to put an open bracket, and I'm going to select the exact same way I did with Jeff's points. I'm going to select the assigment values, putting my cursor right underneath gallery walk which is the first assignment for this class. I'm going to highlight all the way out to the end of the grade book. Hitting Enter is going to tell me we found a typo in our formula and try to correct it. Do you want to accept this? What the typo is, is they've added a closing bracket. And that's okay. We need the closing bracket there, so we can go ahead and accept this correction.
I can now see that Jeff has a grade of 17,463. Now that doesn't seem like a very good way to denote the grade. What we need to do, is turn Jeff's grade into a percentage. We can do this very simply by right clicking on the grade cell, choosing Format Cells. Coming over to the number category in the top of the format box. And choosing percentage, you can see that it automatically calculates the number in to 100 and 7500% with two decimal places.
I can go ahead and click okay and Jeff's grade is now calculating appropriately now, what would be really nice is if we could just highlight Jeff's grade, copy Jeff's grade and paste Jeff's grade. But you can notice that it's giving me an error message. And so let's take a look at this error message and figure out what's happening. If I click on the error message, I can see that the sum is indeed E5 through AH5, which does represent the numbers of grades that Michelle earned in this class. However, it's not being divided by the points possible. It's being divided by E3 through AH3, and it's not what we want.
We want it to be divided by E2 through AH2. Well, thank goodness, Excel has a way to work around this. If I go back to Jeff's original grade that we calculated, and highlight that cell, I can see that his summations are correct. What I'd like to have happen as I copy and paste, I'd like the sum of the grade to change one number as I paste it through the grade book. What that means is I'd like Michelle to be E5 through AH5. I would like Michael to be E6 through AH6, Jack, E7 through AH7, and all the way down.
However, even though I want these numbers to change, I don't want these numbers to change. And we can do that by using the dollar sign. In front of the letter and the number, I'm going to hold Shift+$ in front of the E. Shift+$ sign in front of the two. And that's going to go through Shift+$ in front of the and Shift+$ in front of the two again. What this is telling Excel to do is as I copy this formula, it's going to allow the E4 through AH4 to continue to add plus one as I paste it one row below each time.
However it's going to lock the E2 through AH2 so that never changes even when this is copied and pasted around the Excel spreadsheet. Let's go and see how that plays out. I'm going to Cmd+C to copy Jeff's grade, I'm going to highlight down All of the grade columns to the very end of the gradebook, where Cordelia is at, and I'm going to use Ctrl+V to paste. I can now see that if I highlight any particular cell, while the numbers are getting bigger as I go down, which is what we wanted.
The second sum or the divisor is continuing to remain as the summation of all points possible for each of the assignments. So we've officially added a function that'll calculate great for us and now we can go in and start adding some style to our gradebook.
- Creating lesson plan templates
- Creating worksheets with math equations, charts, and graphs
- Grading papers
- Creating a gradebook in Excel
- Creating an animated presentation
- Setting up a school email account in Outlook
- Storing documents online with SkyDrive
- Creating a class website with SharePoint
Skill Level Intermediate
Q: This course was updated on 10/01/2014. What changed?
A: We added a brand new chapter on Office Mix, the PowerPoint plugin that allows educators to record interactive presentations and test students with quizzes.
Q: This course was updated on 10/15/2015. What changed?
A. We added videos for OneNote, OneDrive, and Office Online. OneDrive replaced SkyDrive as the cloud-based file service.