Numbers for iOS Gradebooks
by Joey Feith
Although I use quite a bit of technology in my teaching, there is one tool that I absolutely could not live without: my Numbers for iOS Gradebooks.The gradebooks that I build in Numbers for iOS allow me to stay on top of my assessment, to better understand where each of my students are at in their learning, and to provide the best possible feedback to my students at any given time.A long time ago, I blogged about how I set up these gradebooks. However, a lot has changed since then (including several new releases of Numbers for iOS). That being said, I felt that it was a good time to share an update on how I currently set up my gradebooks.What is Numbers for iOS?Numbers is part of Apple’s iWork productivity suite. It is a spreadsheet application that can be compared to Microsoft’s Excel or Google App’s Sheets.
There are some differences between how Numbers works compared to other spreadsheet applications. First of all, when you create a new Numbers document, you’ll find yourself looking at a single table placed on a canvas (as opposed to just one gigantic, never-ending spreadsheet). The table can be moved around and/or duplicated as many times as you like.
Think of it this way: when you create a new Numbers document, you’re given a nice clean desk with a printed table on it. You can print out more tables and move them around and/or arrange them however you like.
The other nice feature of Numbers for iOS is the fact that you can view data within your tables as a form instead of just as tables, but we will get to that later.
What are Numbers for iOS Gradebooks?
Basically, my gradebooks are just spreadsheets in which I keep all of my students grades and progress reports.
Within a class’ gradebook, I will have tables for every unit that I teach throughout the year. Each table is set up in such a way that allows me to keep track of my students’ progress throughout the unit. The tables also include formulas that allow me to automatically keep track of how my students are doing in terms of actual grades.
Here’s an example of what a gradebook looks like (keep in mind this is just a demo gradebook and not actual student results):
As I mentioned before, one of the big advantages of using Numbers for iOS is the fact that you can easily create a form for each table within your Numbers document (this feature is only available on Numbers for iOS).
Once your gradebook is set up, Forms allow you to look at each student’s results within a unit individually. You can quickly scrub through students by sliding your thumb up or down along the scrub bar on the right hand side.
Here’s what form view looks like compared to sheet view:
So now that you have a better idea of what a Numbers for iOS Gradebook looks like, let’s go over how to set one one (formulas and all!)
Creating your Numbers for iOS Gradebook
Step One: Download & Launch Numbers for iOS
Step Two: Create A New Spreadsheet
Step Four: Give Names To Your Spreadsheet, Tab, and First Table
Providing everything with names now will help you stay organized later. Also, if you don’t provide a name to each table, you won’t be able to create a form from that table later on.
Step Five: Use The Header Column For Student Names
The header column is the shaded column on the left hand side
Steps Six & Seven: Use The Header Row To Create Outcomes/Critical Elements/Grades/Comments Sections & Format Table Body Cells
There are several ways to do this, and it all depends on how you will be assessing your students and the type of cell formats you will go with. Here are the three main ways I set up my header rows/the cell formats I use.
1. Critical Elements & Checkboxes
When my students are working on specific skills in class, I usually break down each skill into different critical elements (like it’s shown within SHAPE America’s National Standards and Grade-Level Outcomes publication).
The easiest way to keep track of which critical elements my students are demonstrating and which ones they need to focus on is by setting up my cells with checkboxes. Here’s what that would look like:
You’ll notice that in this setup, I have “Skill” in the first header row cell and then the critical elements for that skill in each of the following header row cells. Again, through experience, I have found that this is the easiest way to set this up when using the checkbox format in your table’s body cells.
Checkboxes work great when you can break down the skill you are assessing into critical elements, since demonstrating the critical elements of a skill in your performance is pretty binary (it’s a yes/no situation).
2. Outcomes & Star Ratings
When I’m assessing outcomes that cannot easily be broken down into several yes/no critical elements, I’ll use the star rating format in my table’s body cells instead.
This works especially great since my classes are familiar with the 5-level “Cupcake” analogy to help them understand where they are at in their progress towards mastery of the outcomes we are working on. You can learn more about the cupcake analogy in this great post by Sarah Gietschier-Hartmann (although you’ll notice Sarah uses a 4-level scale whereas I have always used a 5-level one instead).
Here’s what a table using the star-rating formatted cells would look like:
In this setup, I only put the titles “Outcome” and “Progress” in the header row and then put the full text of the outcome I am assessing in the table’s body cells. Again, this will make more sense once you see how it looks in the form view.
3. Outcomes & Pop-Up Menu
So what do you do if your assessment scale cannot be represented by binary checkboxes yet also cannot be represented by a 5-level star rating format? Well, for everything in between (or beyond), you can create a pop-up menu. Here’s how:
Step Eight: Set Up Your Automatic Grade Formulas
One of the advantages of having all of you assessment data in a spreadsheet is that you can then apply formulas to automatically calculate the final grades for you. Here’s the formulas you will need depending on the cell format you go with:
1. Outcomes & Star Ratings/Pop-Up Menus
Creating the formula that will calculate the grades for these types of cell formats is very straightforward. Numbers reads a star rating cell as a value between 0-5. As for your pop-up menu, it will read it as the numerical value that you assigned to the pop-up menu options.
That being said, all you need to do here is set up a formula to calculate the sum and average of the cells that contain a score (in this case, it will be SUM(C2,E2,G2)/3).
Once you have that formula set, all you have to do is copy the formula and paste it into the other students’ “Result” cells like this:
This process would be the exact same for tables using the pop-up menu cell format.
2. Critical Elements & Checkboxes
If you set up a table using checkboxes in the table’s body cells, then the formula gets a little bit trickier. The reason this is true is because of the fact that Numbers doesn’t read an unchecked box as a “0” and a checked one as a “1”. Instead, it reads unchecked boxes as “False” and checked ones as “True”. Therefore, since the app cannot automatically calculate an average of “True” and “False” entries, we’ll need a slightly more complex formula: COUNTIF(test-array, condition)
This formula looks at the checkboxes and gives a score of 1 for every “True” entry (“False” entries are scored as 0). This will allow you to use the checkbox results as a numerical value. For it to work, the “test-array” value should be the checkbox cells for that student (in this case, C2:G2) and the “condition” value should be set to “true”.
Once you’ve set this up for your first student, all you have to do is copy/paste the formula to the rest of your “Result” cells.
Step Nine: Creating Forms
Now that you have your unit’s table all set up, it’s time to make a form. Remember that this only works if you’ve given your table a name (the latest version of Numbers for iOS automatically provides each new table with a name which you can then modify).
To create a form for a table so that you can view it in the form view rather than the sheet view, all you have to do is tap on the “+” symbol in the upper left corner and select “New Form”. From there, just select the table you want to create a form for.
You can create a form for every named table in your gradebook.
Step Ten: Creating A Term Grade Table
The grade I assign to each student in my physical education classes represents a combination of the grades for the various units we did throughout the term. Therefore, I always create a “Term Grade” table to help me automatically calculate the final grade for that term.
Setting this table up is actually very easy (then again, I’ve done this a 1000 times…)
Start off by creating a basic table with your student names in the header column and the names of each unit/standard you assessed in the header row (save yourself time by copy/pasting one of your tables and just deleting all of the table’ body cells data).
For each student, you’ll want the cell under each unit name/standard number to go grab the final result from that unit’s table. Here’s how you do that:
See? All it’s doing is grabbing the value from the student’s result cell for that unit. Since it’s a formula (=Fundamental Movement Skill Unit::Result Joey) you can then just copy/paste the cell’s formula for all of the other students:
Repeat this process for the other units that you completed within that term.
Once this is done, you’re going to want to create a formula to calculate the overall term grade. If each of your units we’re weighted the same, the formula will be very simple: =SUM([Result Cells])/Total # of Units
However, if your units are all weighted differently, then your formula will have to be more complex.
Let’s say my Standard One unit is a lot heavier than the others, so I want it to be worth 50% of the final term grade. If my Standard Three unit is slightly heavier than my Standard Four unit, then I’ll probably want to make that one worth 30% (which leaves 20% for my Standard Four unit). In this situation, my formula for Joey (the first student in my table) would be =((Standard 1 Joey)x.5)+((Standard 3 Joey)x.3)+((Standard 4 Joey)x.2)
This would give me a final result out of a scale of 5 (which is how we operate at my school to determine percentages… I’ll get to that in a second). If you wanted the final grade to be a percentage, you could use this formula instead =(((Standard 1 Joey)x.5)+((Standard 3 Joey)x.3)+((Standard 4 Joey)x.2))/5 and set the cell format to “Percentage”
Step Eleven: Providing A Letter Grade With VLOOKUP
Alright: now for the grand finale.
As I mentioned earlier, my school works with a competency scale (we us the term “competencies” instead of “standards” here in Quebec… we also like gravy on our french fries). The way the scale works is based off of a scale from 0-5 (0 being that the student shows no degree of competency in regards to the outcome, 5 being that the student demonstrates an above average level of competency in regards to the outcome). This is why all of my grades/results have been out of five in my gradebook.
Depending on what degree of competency a student is evaluated at by the end of the term, that degree of competency will translate into a grade based on the scale of competency we use at my school.
To make things simpler here, I will use a letter grade competency scale (which I will call the standard scale) to demonstrate how I get Numbers for iOS to automatically convert a final result into a grade.
First off, you need to make a new table in your gradebook that outlines the standard scale.
Once that is set up, you will need to make sure your Term Grade table has a “Grade” column.
For your first student (in this case, Joey), select the “Grade” cell and enter this formula: =VLOOKUP((Results Joey), (Standards Scale::A2:B10), 2, (close match))
Here’s how you do it:
The VLOOKUP formula works by having you set the original result you need converted (“search-for” which, in this case, is Joey’s “Result” value), then having it look at all of the values within a table (“columns-range” which is our standards scale table), then having it select which column in that table represents the desired conversion (“return column” which will be “2” since the letter grades are in the second column), and then having it decide which result is the closest match (“close-match” which will automatically calculate which letter grade is closest to the original result score).
And that’s it. That is how I set up my Numbers for iOS gradebooks for each term. A few things I would like to mention before I publish this monster of a post and go to bed:
1. Yes, there are apps out there that could do all of this for me. I’ve tried a lot of them and kept coming back to Numbers. There’s something irresistible about being able to totally customize your gradebook in a way that meets your needs every time. That being said, this system is probably not for everyone.
2. No, you cannot send a form view page to a student. You can send a screenshot, but that’s a bit of a hack of a workaround. What you can do is always have your gradebook up to date so that you can have meaningful discussions with your students about their progress.
3. Always have your gradebook set up at the beginning of your unit and keep it up to date as much as you can. Make no mistake: a properly maintained gradebook is an incredible tool when it comes to planning lessons that meet the needs of your students and providing your students with meaningful feedback that will help them progress.
4. All of you Numbers for iOS documents sync automatically to your iCloud account. Yes, you can access them on the desktop and web versions of Numbers. No, you cannot view your tables in form view except when working on iOS. No, there is no Android/Windows version of Numbers.
5. The most difficult part of setting up your gradebooks is that first term. Once you have your tables and formulas set up, everything else is just a lot of copy/paste. Don’t be intimidated by the learning curve: you’ll pick all of this up very quickly. The math involved really isn’t complex or difficult to master at any point. Have fun with it, assess your students on a ongoing basis, and be the best #physed teacher you can be.
Thank you for reading and happy teaching!
May 3, 2017
May 3, 2017
March 14, 2017