In our last post, which introduced the Power Law, things were getting kinda hectic:
We'd looked at a whole bunch of stats, talked about our friend from high school algebra (formula for slope), and started to repurpose the original gradebook I posted. It was like old home week around here. But we're not stopping there. Nope. It's time to develop graphs for this data. So, let's take a look at the reporting tool in the workbook. I've made a couple of changes.
First of all, because the Power Law focuses attention on the most recent score, the way we look at student progress is going to look different. In fact, if you click on the "Scores" part of the workbook, you'll notice that the columns that used to contain the median scores for a grading period have been removed. In the reporting tool, I've added two items and altered a heading to accommodate the changes. I selected the colour blue to represent "student progress," which is a way to describe the scores determined by applying the Power Law. In other words, black shows the assigned score, and blue the adjusted score. I also replaced the bar chart showing process with the trendline representing the Power Law data. Finally, I changed the head of E10 to be "Learning Trend" instead of "Growth from 1st Quarter."
I'm not going to go through how to create the data validation list for the last name or the whole graph build. If you need a refresher on those items, please (re)visit the Beginner's series Part IIa (data validation, INDEX/MATCH) and Part IIb (graphs). I'm just going to show you the new stuff.
Here's the first row (Row 11) for Steve Canyon's performance. In cell C11, I've used the INDEX/MATCH function to report the most recent score (not an average or median) from the report for this standard. Our "All Scores" graph looks the same as in the original Beginner's Gradebook, but in cell E11, we can see the results of the Power Law plotted out for comparison with a Steve's scores. As you can see, this gives us a different view of student performance. His actual scores jump around, but the overall trend is one of moving toward the standard.
Here are a few other examples for your consideration. In these cases, I've plotted both the student's actual scores (in black) and the predicted scores using the Power Law (in blue).
Gonna Fly Now...
Got Viagra?
Patient flatlining. Clear!
The Power Law can provide a different sort of summary of performance vs. simple measures of central tendency, and can generate different kinds of discussions with stakeholders. Also notice that the learning curve doesn't always head in a positive direction. In the middle example, there's a student who has met standard, but never improved on the performance. And in the bottom graph, you can see that inconsistent performance leads to no curve at all.
Some commercial gradebooks have a Power Law option. It's certainly a lot simpler to have a built-in formula at your fingertips. However, I don't want you to think that you have to spend a lot of money to get this sort of information. And, I firmly believe that you should be in control of your data. You're the expert about your students. You can download the final version of the Power Law Gradebook, which has all of the report completed to use as a model for your own data sets.
I had a request for an Excel Gradebook that used Marzano's Power Law to determine grades. So without further ado (press play for a musical accompaniment for your continued reading):
You've got the power!
What's the Power Law?
I'm glad you asked, although I don't know a whole bunch about it. If you want a deeper look, check out Transforming Classroom Grading, which goes into the research behind the Power Law of Learning and provides a step-by-step for using it in calculations. But, lemme sum up.
If you were to plot out the scores of a student learning a skill which was brand new to them, which graph do you think would represent their scores over time---the one on the left...or the one on the right?
Inquiring optometrists want to know: "Which looks more clear: #1 or #2?"
Both lines have the same start and end point. They describe the same number of assignments. But one is a better representation for what happens during the learning process. Did you guess the one on the right? Yup. You were correct. When new skills are introduced, there is a big gain in learning at the beginning. With repeated opportunities, learning still increases, but at a much slower rate. Devotees of the Power Law believe that the most recent score for a student is the what should be reported as a grade.
Okay. So now what?
Oh, that student scores followed the exact same trajectory every time. But that's not what happens. Learning is impacted by all sorts of factors both inside and outside of the classroom. The graph above shows the average increase. We need to be able to make an individual student's scores apply to that learning curve. This is where the Power Law comes in handy. Here it is:
Hello...Newman...
It might have been many moons since high school algebra, but perhaps the Power Law reminds you of the one used for slope: y = mx + b. Makes sense as we are trying to look for a trend in learning. In our Power Law formula, y is the student score that is predicted and x is the original student score. The roles of m and b are to act as constants. They take into account the number of scores for a given standard and the position of a score within that range.
Calculating m and b requires some statistics, because in a way, we're "normalizing" the data set to the curve. If it's been awhile since you had to deal with this sort of thing, remember that the "E" symbol means "sum of" and "N" = "Number" (in this case, referring to the number of assignments for a given standard. Don't get too sweaty looking at these, because our buddy Excel is going to do all of the heavy lifting for you. I just want you to see them before we look at the gradebook.
omgwtfbbq
And now, a musical interlude:
Back to our regularly scheduled workbook...
I'm going to build this workbook using the finalized Excel for Beginners workbook. Keep in mind that these formulas will work the Intermediate and Advanced versions, too. Just plop the formulas any old place and they'll do your bidding. Here is the first chunk of student scores:
Now, we need to transform them using the Power Law. I've set up a column for the variables we're going to use: x = (natural log of) Assignment #, y = (natural log of) Student Score, then the various combos we'll need using these. Please note that they here is not the same as the one in the Power Law itself---it is just a statistical variable assigned to calculate m and b. (Confused yet?)
And here are the formulas generating these values:
I've probably made this look far more complicated than it needs to be, but I also like the idea of breaking down the steps---especially if you're new to Excel or just need the opportunity to follow along with what is happening. The formulas in D26 and D27 use the natural log (LN) to transform the ordinal number of the assessment (x) and the student score on that assessment (y). Cells D28 - D34 are used to process x and y so they are ready to use in our equations for m (D35) and b (D36). The "predicted score" showing in Row 22 shows you the result of the Power Law formula (y = mx^b) for each student score.
Here is the link for the Power Law workbook with formulas. We'll talk about the graphs for this function in the next post, as well as reveal the final version of the workbook. Download this one first so you have something to play with.
Do you have mad skillz when it comes to Excel, SPSS, or other data tools? Know a regression from a box plot? Are you anal retentive when it comes to cleaning and organizing data sets?
Then you should consider becoming a volunteer for Data Without Borders. From their site:
Data Without Borders seeks to match non-profits in need of data analysis with freelance and pro bono data scientists who can work to help them with data collection, analysis, visualization, or decision support. Big companies like Google and Amazon recognize the importance of dedicated data science teams and can support fulltime analysts, but non-profits, though they may have rich and interesting datasets, don’t have the resources to capitalize on their data or may not even know the value of the data they already collect. Data Without Borders aims to close that gap through a data scientist exchange, bringing exciting new problems to the data community and helping to solve social, environmental, and community problems alongside nonprofits and NGOs.
They have a Datadive coming up in San Francisco early in November. I watched the NYC Datadive recently, and was both fascinated and deeply heartened by the work that weekend. You can check out the project wiki here. It provides you with links to each of the presentations by the NGOs/non-profits, as well as project pages where you can get an idea of the scope of the work that was completed 48 hours later.
Data Without Borders has many different options for volunteers. So, if you're not into coding, don't know a bar graph from a pie chart, or are prone to fainting at the sight of a normal distribution, fear not. There are still opportunities for you to support others in this work.
You might be interested in the Redesign the Report Card contest happening over at Good. You can vote on one of seven entries through Sunday, October 23.
The standard report card is in need of a major redesign. Grant Wiggins once opined that report cards should be more like baseball cards. I agree. Not only has the basic report card format remained more or less unchanged over the last 150 years, it rarely reflects the whole child. While the Roll Your Own Gradebook series here is an attempt to look at a different way to display student data, it is only a first step toward giving teachers control of the data they have and how they share meaning with others. We still have a ways to go to making things pretty.
But click on over and take a look at what has been suggested. Here are the two I liked best.
This submission, by Polly Avignon, uses colour-coding for assignment categories and simple graphics to communicate a variety of data: scores, attendance, observations. (Click here for a larger version.)
I also like this one by Larry Buchanan (larger version here):
Although I personally wouldn't choose to display the class average (or any average at all, for that matter), I do like the area graph with comments. I also like the separation of grades from student behaviors, such as participation and effort.
Each comes with a screencast tutorial and links to both the "before" and "after" version of the gradebooks. Make classroom data dance to your tune and shake its little moneymaker for your stakeholders using Excel.
In the intermediate series, we integrated two sets of data into one report. But let's face it, most teachers do not have the luxury of teaching two classes or subjects. If you're an elementary teacher, you're juggling multiple subject areas and assessment sources. Secondary teachers have several class periods. You can still use a single reporting tool. You just have to master one more formula: the nested IF statement.
You already know how to construct an IF statement. In fact, if you've been following this series, you've already done a fairly complicated one involving the addition of INDEX and MATCH. This time, we're just going to expand that to "nest" (read: embed) a couple more IF statements in the formula. You can download the gradebook for this tutorial here.
In this workbook, there are 4(!) classes. We still have biology and chemistry from the intermediate series, and now we have physics and earth science. We also have the same Report we've been using since the very beginning. I've already added the named ranges for you in the Formulas worksheet. You will not need to update the data validation lists for the report.
In the Intermediate gradebook series we used the following formula to fill in the cell marked in yellow: =IF($C$4="P1 Biology",Formulas!B2,Formulas!C2)
We had two classes, which fit perfectly into the plain Jane IF statement: a place to tell Excel to look if the first part ($C$4="P1 Biology") was true (the first cell with a biology standard), and a place to look if the first part was false (the first cell with a chemistry standard).
Now, we have to tell Excel to look in one of four places. Oh noes! It doesn't fit in an IF statement anymore. But we can fool Excel with an Inception-style solution: an IF statement within an IF statement within an IF statement.
Here's what it will look like: =IF($C$4="P1 Biology",Formulas!B2,IF($C$4="P2 Chemistry",Formulas!C2,IF($C$4="P3 Physics",Formulas!D2,Formulas!E2)))
Here's what it means. The red text below shows the beginning of our IF: the statement to evaluate ($C$4="P1 Biology") and what to do if that is true (Formulas!B2), but instead of directing Excel to another cell for a false statement, we feed it another IF. This is the statement in purple, which leads to another false statement in blue. Once you have this formula in place for the report, use your fill down option (look for the crosshairs when your cursor is placed in the bottom righthand corner of the cell) to complete the three cells below.
That's not so bad, is it? Keep in mind that you can have up to 7 IFs nested in a single statement. If you have more than 7 options, you will have to use a workaround that we can talk about in another post.
Okay, now you need to take a deep breath and modify the IF/INDEX/MATCH statement from the Intermediate series of tutorials. The bad news is that this is going to look a little scary. The good news is that you only have to do it once. After that, you can copy and paste the formula into other cells, then just edit the columns you need.
Let's start with the box for the student's first name, shown in yellow in the diagram shown below.
In the Intermediate series, we used the following formula: =IF(C4="P1 Biology",INDEX('P1 Biology'!B8:B17,MATCH(C6,P1Biology,0)),INDEX('P2 Chemistry'!B8:B17,MATCH(C6,P2Chemistry,0))). This time, due to juggling four classes, you're going to have to manage a much larger formula. You've trained for this. You can do it.
It's not as bad as it looks. See the pattern? IF, INDEX, MATCH...IF, INDEX, MATCH...Lather, Rinse Repeat. And you know what? For all the "Current Scores" cells, you only need to change the highlighted columns in the formula.
When you're ready to fill in the formulas for the dynamic graphs, you can copy and paste the formula again, but you will have to make a couple of adjustments. Excel won't know that cells C4 and C6 refer to the Report worksheet unless you tell it. To make the fill go more easily, make some attributes absolute (with the $) so that Excel only changes the columns you want it to change. Here is an example for cell H2 of the Formulas sheet:
because only the Chemistry and Physics worksheets have assignment scores in Column J (Biology and Earth Science have median scores in those columns). The "" at the end of the formula tells Excel to leave the cell blank if it is not a Chemistry or Physics class. If you're still a little confused about this part, you might want to go back and review the last part of the Intermediate series.
Here is the YouTube version of the tutorial. Enjoy!
Don't be sad that we have reached the end of the gradebook build. There will be plenty of new permutations to explore. (I've already had an inquiry about adding in the Power Law formula.) We will also look at other types of dashboard reports to summarize activity in your classroom. Keep leaving your ideas and suggestions in the comments and we'll keep on trucking with this.
Bonus Round
Does the thought of managing these formulas make you queasy? Remember that you can just use what's included with the Beginner's series and create a separate workbook for every class or subject. Or, if you have more than one period of the same subject, list all students in the same worksheet. This will decrease the number of IF statements you need to develop.
I'm starting to put together a workshop on Excel dashboards that will be in December. I'm playing around with a few different data sets and options, and have a quandary I'd like your help with: What is the best layout option when using stacked bar charts?
If you don't know what a stacked bar chart is, here is an example:
There is one bar representing 100% of whatever is being measured: numbers of students, percentage of scores, speeds of African and European swallows, and so forth. In the stacked bar chart shown above, there are four categories which have been numbered. Instead of each category having its own private bar, they get together and party in one space. The size of the categories, indicated by different shades of green in the example, shows their proportion of the 100%. It is a simple way to compare the relative sizes of categories...and all without some ridiculous pie chart.
But stacked bar graphs do not have to take things lying down. They can be vertical, too. Which leads me to my problem: When should you use the horizontal format...and when should you use the vertical format? My Google Fu hasn't turned up any rules. Seems like everyone is just letting it all hang out.
There are three layout options shown below. I know I don't have the categories labeled, but I just want to consider layout at the moment. Just FYI, the data represent scores on the 2011 Washington state test for reading. The shades of green, from lightest to darkest, show the percent of students at a school who scored at Levels 1, 2, 3, and 4.
The first layout has a very traditional look:
Labels for each grade are to the left. The graphs are (more or less) placed so that you can make a quick comparison among the grade levels. But is this better?
Same data. Same graphs. Just rotated 90 degrees. I don't know about you, but I find this easier to "read" when looking across the grade levels. It's true that there would be some issues with how closely the graphs can be pushed together due to the labels, but the overall format is okay.
And finally, my least favourite, but deserving of discussion is this:
Considering that there will be more graphs for mathematics, science, and writing, would it be more important to look at performance for a subject area across and review data for a grade level along a vertical axis (not shown here, but imagine there are stacked bar charts for the other subject areas underneath).
What do you think? Just from the standpoint of layout, which format is most meaningful for you?
Welcome to Spreadsheet Day 2011 on Excel for Educators. Personally, I think every day should be Spreadsheet Day. But then, I was also disappointed when I couldn't change my Facebook status to show that I am in a relationship with Excel.
The theme of this year's Spreadsheet Day is to help students. I have been traveling for work the past two weeks, and so my preparations for this day have not been made. I will, however, have a post later this week which finishes fleshing out the standards-based gradebook for teachers.
What I would like to share today, however, is a word of encouragement. You see, Excel and I were not always BFFs. In fact, I used to be afraid of even clicking on its weird-looking green alien icon. And then I found myself teaching a class where we were going to help chemistry students do a regression. I had to man up, as it were, and dive into the world of spreadsheets. And I've happily been there ever since.
I wish I had known all of the things I could have done with Excel to help me as a teacher. Not only for tracking student scores, but for all types of record-keeping: attendance, calls to parents, student behavior notes, observations, tracking time for lessons. I could have used the graphs to look more closely at student grades and reflect on what was happening. And, I could have helped students do more with Excel. I was a science teacher, for crying out loud. We could have integrated spreadsheets with so many labs.
So, my advice for today is simply to go forth and double-click. Open Excel and play around. Think about the ways it could automate some of the tasks for your classroom and change your workflow. And if you don't know how to make Excel bend to your will, ask me or someone else who can help. We'll learn with you. Can you think of any better way to model the power of spreadsheets for your students?
Debra Dalgleish says that next Monday (October 17) is Spreadsheet Day. So, you know what that means: It's time to get your nerd on.
Her challenge to us this year is to create a template or add-in to help a student. (If you don't have time, drop by her blog on Monday and leave a helpful tip for others.) What say you, educators? Can you spare a formula? Perhaps a free Excel sample to hook a young impressionable student? Because if you don't talk to your kids about spreadsheets...who will?
Debra provides the following instructions, for those of you who wish to participate:
Next Monday, October 17th, post your Spreadsheet Day contribution on your blog, or Facebook, or Twitter (use hashtag #spreadsheetday), or create a public Google spreadsheet. If you send me a link to your free and useful Spreadsheet Day tool, I’ll post it on the Spreadsheet Day Blog, to help students find your work.
Please do go read the full post. There's still plenty of time this week to ask your students what sort of tool they would use, if offered. Then, be fruitful and let Excel multiply!
Nearly two years ago, I had an assistant superintendent of a large school district tell me that one of his biggest wishes for displaying data would be to overlay his student achievement data with a Google Map. At the time, I thought it was a very intriguing idea, but at the time, I was unaware of any tool which would automate that process. It seemed unlikely that anyone would actually take the time to build a map in Google Maps (or Google Earth), student-by-student. I had seen visitor maps on websites that somehow captured IP addresses and then pulled them into a map displayed on the sidebar, but I was sure that required way more code than I was interested in dealing with.
In the intervening time period, I have discovered several tools for mashing a spreadsheet of data and a Google Map. The first link sent my way was for MapAList: "a wizard for creating and managing customized maps of address lists."
I pulled some public data off the state website and stripped off what I needed (name of school, address, score on 10th grade 2010 math test) in Excel. I then uploaded the spreadsheet into GoogleDocs and logged into MapAList. After fussing a bit with the settings, this is the result (or click this link):
What you're looking at is a map representing nearly every high school in the state and their performance on the 2010 10th grade state math test. (Be sure to zoom in so you can have a better view of things.) My divisions by percent meeting standard are arbitrary. Perhaps other pieces of data would be more valuable to show. But for proof of concept, it's a start.
I do find it interesting to see just how much the Cascades really divide our state geographically---there is a clear division of east and west with nary a school district in sight. The map also gives an interesting view of population. While not every school is the same size, the effect of clustered pins provides a different way to think about distribution. The yellow and green "outliers" definitely grab your attention. What's happening in those schools that are all by themselves (in terms of geography) and are doing all right?
Right now, you are restricted to two pieces of data/information showing in the pop-up for each pin. This is a bit of a limitation---I would like to show school district name or % free/reduced lunch or size of school or ethnicity data in addition to school name and score. The tool is also clunky if you want to go back and change any settings---for the most part, you just have to start over. The map will autoupdate if your spreadsheet changes, you're just stuck with the labels and appearance of the things.
As an educator, what are the uses for a tool like this? Might I want to mash my district achievement data (however that's defined) with a map? Would I see intriguing things as the neighbourhoods changed or gain other insights? I do believe that one would have to be very careful of running afoul of FERPA. I'd want to leave student names off the spreadsheet---they're unnecessary in some ways if the goal is just to visualize the interaction between geography and achievement.
I mentioned at the beginning that there are other tools which will accomplish similar goals. We'll take a look at those in future posts, as well as add the dimension of time to mix.
There are a lot of Excel tips and tricks out there---whole blogs, YouTube Channels, message boards, and more devoted to all of the things that make Excel such a versatile piece of software. While the purpose of this blog is not to replicate all of that amazing content, I do want to pull out ideas and functions that educators might find the most useful.
Have you ever set up an equation in Excel and gotten an error message---such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!? Total buzzkill. On one hand, these alerts serve a greater good. They let you know if your formula has gone awry. And, on the other hand, they can show up in an embarrassing places.
In our Roll Your Own Gradebook series, we made the assumption that we had classes of Stepford children: every student completed every assignment. But let's face it, that's not what really happens. For example, let's say that Flash Gordon wasn't enrolled in the course for the first quarter. If there are no scores, then Excel gives us an error message when it tries to apply the formula:
Uh-Oh, Spaghetti-O
You can change this, without altering the outcome for other students, by using IFERROR. This function tells Excel to evaluate what's happening, placing one value in the cell if there's no error (e.g. score for Stepford child) and another if there is an error message (e.g. "Trash" Gordon, lazy athlete). In short, it allows you to bypass the error message.
How It Works
You could apply the formula to more than one location in the gradebook and get the same result, but for now, let's look at the worksheet with the scores.
Superhero Behaving Badly
The cell on the right---the one with the "###" is the one we need to address. The current formula is =MEDIAN(H14,J14). Alas, there are no scores to find the median for---hence the error.
Instead, we can use =IFERROR((MEDIAN(H14,J14)),"") The double double-quotes at the end tell Excel to leave the cell blank. However, you can put another value there or even a text string (e.g. "No Grade"). Here is what we see now:
Ahhh...That's Better
What do we see on the Dashboard?
A blank. Wow. Excel is doing just what we told it to do. Imagine that.
Recently, I was working with some educators who didn't know a pie chart from a hole in the ground. It was actually worse than that, but as we see more and more information communicated via charts, graphs, and other data visualizations, there is also a corresponding literacy needed to read and write these. And while my internal monologue may run along the lines of "OMG...you can't interpret a bar graph?!", my overall goal is to make data viz accessible to educators. Sometimes, that means I will have to start at the beginning.
But fret not if you don't know a bar chart from a scatter plot---let alone when to use one. Tuck a copy of the Choosing a Good Chart diagram in your back pocket and you'll be ready for action.
Featured on the Extreme Presentation Method blog, the chart is available in several languages. What I like about this tool is that it starts with "What would you like to show?" The purpose of the communication is front and center. This can really guide the thinking of beginners. Even if they're not familiar with all of the charts they see on the page, it's a way to broaden their view beyond pie charts and line graphs.
Bonus Round
For a more interactive way to select a chart, try the Chart Chooser over at Juice Analytics.
