The Agony and the Ecstasy

Am I the only one who agonizes over the best way to represent a data set? Is there a 12-step program for those of us who are occasionally paralyzed by all of the visualization options? If this sounds familiar to you, read on for my latest struggle in bringing a data story to life.

Several weeks ago, I was asked by my superintendent for information on the achievement gap. For those of you who might not know this particular piece of eduspeak, it refers to the difference in achievement levels between populations of students. For example, white students often perform better on standardized tests than black students. This difference is referred to as the achievement gap.

This request should have been a piece of cake. I have the data (scores and student demographics). I'd done a similar project last year. But I felt like the bar charts I'd used were lacking. They show differences in performance plainly enough, and yet it's difficult to capture information for various populations in a single, easy to read report.

I started as I usually do, hand drawing some possible layouts and then building a few models using the data.

These examples all show longitudinal information for males and females at a particular grade level and in a certain subject area. The particulars are not too important here. What I discovered in doing these, even before cleaning up the charts, is that none of them were satisfactory. They all showed the data accurately, but none of them captured "the gap" in a way that caused any sort of interest or reaction.

Back to the drawing board.

I realized that I needed Excel to show me the percentages on one axis, like a number line, so the space...the gap...became visible. Here is what I ended up with:

This is a bit out of context, so let me tell you a bit more about what you're looking at. This chart only shows 2015 data for one grade level. The horizontal line is the number'ish line: 0 - 100%. The vertical line shows the overall percentage of students who met the standard on a particular assessment. The placement of populations (shown by orange or blue triangles) provides a general relationship to that overall level of achievement, as well as shows the gap between the populations. I do include the actual n sizes and percentages in a table below the charts.

Here's a broader view:

I am not going to show you the data tables, due to FERPA issues---some of the subgroups shown above have fewer than 10 students. I need to stay within the bounds of federal privacy laws in this public space, but just know that they exist to provide detail to data users in our district.

I'm really happy with this layout, however. It gives, at a glance, an easily readable view of the achievement gap at a grade level. When looking at these over several grades, patterns begin to emerge. This is especially important for those groups where the n size is very small for a grade level. For example, having only one black student in a grade might not tell you much if they didn't meet the standard, but when you see that our small handful of black students at every grade level all fall well below their peers, it's alarming. It's also easy to cover up either the orange or the blue markers and get a quick picture of who is or is not successful.

While I still have the longitudinal view to consider, it's simple enough to build similar charts for a few years of data and then align them to provide a similar glance at trends.

I apologized to my superintendent for my tardiness in delivering the product, but I think the agonizing has given way to some ectasy over seeing things in a way that's clear and best represents the question to be answered.

I don't know that anyone, other than those of us struggling to represent data, understands why it takes so much time to build a report. Others don't see how many different charts we modeled...all of the colors we tried...or the variety of label placements (and label content) we viewed. They don't hear the conversations with have with people around the office to learn more about what is or isn't working for them in our draft visuals or how they want to interact with and use the information presented. But for those of you who are knee-deep in this process, I'm cheering you on from here.

Bonus Round
Like these charts? They're just scatter plots in Excel, with the vertical axis removed. Easy-peasy to make if you're on the hunt for something similar using your own data.

Anatomy of a Design Build

Like many school districts, we have new data this fall. The state has changed to Smarter Balanced assessments to measure student knowledge and skills with Common Core State Standards. One of the challenges associated with presenting these data is the inevitable effort to compare them with scores from previous years. But they do not represent the same things. And so began the challenge to develop this report:

Subject areas are organized across the top. Grade levels are on the horizontal. The blue squares represent grades and subjects assessed in 2015. The large numbers in the center show the percent of students meeting the standard, with the distribution of scores shown by the column charts at the bottom of the square. Historical data are shown by the line graphs in the grey squares.

It is built in Excel (of course). On another sheet is a table listing the various schools, grade levels assessed, subjects, school years, and scores. A pivot table with this information feeds the report you see above.

I'm mostly happy with this layout and format. I would still like to tweak some of the colours, but overall, I think I've solved most of the issues with representing the data. But it took awhile to get this far.

In the beginning, there was a suggestion from one of my supervisors to offer something like what you see on the right. This is what another district in the area provides to schools and the public. I showed it to one of our principals and he said it made his eyes bleed. I agreed with that sentiment. We can do better, I said.

It's not that the information provided here is bad, it's that a pdf of a spreadsheet does not take advantage of what data visualization can offer to make meaning. It has much of the same information I developed for my version. We just used two different approaches.

Originally, I started with something similar. I pulled  demographic and program data and added some sparklines and arrows to show change.

But I decided that this was a stupid idea before I got too far down the road with it. It was way too hard to scale the graphs along the same axes. This is an important thing to do to enable comparisons. But there is just too large of a range to represent in a small space. Not to mention this layout is boring. Seriously. Yes, I know it's half finished, but no bells and whistles involving fonts and headers are going to make this version anything other than a snoozefest.

So, I tried something else.

This is where I started to play with the idea of tiles. I wanted a cool color (as opposed to a warm one) and decided to look at purple as an option. This version is slightly better, but not by a lot. Looking at it, I realized something very important: Almost none of this information is useful. Does anyone, especially a school leader, really care about the year-by-year percentages of Asian students (for example)? It's not actionable data. You're not going to go out and recruit more Asian children if you notice your percentage slipping. There's no action plan a school would make to decrease the enrollment of girls. This is not to say that viewing overall change wouldn't yield some insights, but the year-by-year review isn't very helpful. So I started over. Again.

Third time's the charm, right?

I won't claim this is perfect, but we've come a long way, baby. All that demographic and program data? Now in one tidy bar chart on the left (under "% of overall enrollment"). The five-year change is shown on the far right. The stuff in the middle? All new. Kids are more than the sum of their test scores. So, I've included some additional demographic information about absences and discipline. Now we have some conversation starters.

I did the achievement data next. This is the page at the beginning of this post. I played around a bit with colors and format, but the tiles have been a constant.

Feedback has been mostly positive, but I'm still tweaking things. What would you want to see? How should we show it? Anything we should remove or represent differently?

Looking at Disproportionality

The concept of disproportionality underlies much of the reform movement for education in the United States. Sometimes referred to as the achievement gap or opportunity gap, the basic idea is that outcomes for all students are not equitable. While much of the conversation focuses on race, disproportionality applies to any subgroup: gender, special education, English language learners, free/reduced lunch, and so on. Over the past several years, much of the conversation about disproportionality has focused on student achievement---and test scores in particular. But just as there is more we need to look at in addition to race, there is more to children than representing them as test scores. We can look at disproportionality as it relates to sports or after school activities, student discipline,  attendance, and other factors.

If you want to examine disproportionality within your system, there are a few pieces of data that you will need to know. In the example below, I'm going to use gender (male, female) as subgroups. (Note: I realize this is a very heteronormative view of gender. Our data systems need to catch up with our increasing understanding as a society about gender identification; however, right now, most school data systems are set up to only capture the binary male/female...so I'm going to use it as an example.)

First, you need to know the enrollment numbers and percentages for each subgroup. In other words, how many possible students are there who could participate in an athletic program, be subject to suspension/expulsion consequences, fail Algebra, or yes, pass the state test? Many schools report gender as close to 50/50 percent, as one might expect, but variations do exist. Don't assume that you're starting off with equal pools of participants.

Secondly, you need to know the participation numbers and percentages for each subgroup. Just because everyone is eligible to pass the state test doesn't mean that they do. So, how many males/females met the standards? How many in each subgroup were suspended? Enrolled in Physics or Calculus? Turned out for basketball?

In this example, we have a school with 250 males and 275 females, with 50 from each group enrolled in Algebra. Now we need to calculate the disproportionality.

To determine the number of males required to achieve proportionality for the total population, we use the first equation described above (n males for proportionality = (50 * .52) / .48) for a result of 45.5 males. The second equation gives us 55 females needed for proportionality.

Next, we take these two and compare them with the number of students in each subgroup that are participating. For males this would be 50 - 45.5 = 4.5; for females 50 - 55 = -5. That -5? It means that we need five more females enrolled in Algebra to achieve proportionality.

While it may not be entirely realistic to achieve perfect proportionality within a system for all programs, subgroups, and outcomes, it is still important to review these data to reflect on areas where institutionalized racism or policies may be contributing to disproportionality. Another factor to consider is the size of the subgroups that you are reviewing. For example, if you only have two or three American Indian students in a grade level, it's unreasonable to expect that they are represented in everything---but you should look to be sure that they are represented somewhere among school offerings. In that case, it may be more helpful to use longitudinal data to get an idea for participation.

Here's an Excel workbook that allows you to easily compare gender equity in sports programs. I built it a couple of years ago for a program that needed it, based off an idea of Debra Dalgleish. See her site for even more ideas on data entry forms...and feel free to modify mine to suit your needs.

Lookin' for Data Love

I recently attended (and presented) at the 69th annual ASCD conference. If you're unfamiliar with the acronym, ASCD is one of the largest and oldest professional organizations for educators. The letters used to stand for the Association for Supervision and Curriculum Development, but that definition was scuttled a few years ago when the organization outgrew its original boundaries. The acronym was kept for branding purposes.

You might remember that I went hunting last year for data tools in the exhibit hall. I did so again, along with attending a couple of presentations on how data is being used in schools. So, here's the wrap-up.

Vendors
I looked at three different tools in the exhibit hall. None were quite to the level of a student information system, but all integrated assessment and performance data. Two are not worthy of further discussion (one, in fact, admitted that they do no testing/accommodations for accessibility).

A third, Schoolzilla, didn't totally blow me away; however, they are using Tableau to build their reports. The reports follow the Shneiderman mantra of "Overview first...then zoom and filter...details on demand." To be fair, I don't expect any product to knock my socks off in an exhibit hall setting and where I spend <5 minutes at a booth. However, Schoolzilla may be worth a more in-depth look, if your district is on the hunt for that sort of thing.

I also spent a chunk of time at another venue chatting with a rep from SchoolCity. Their product is recently undergoing a complete redesign, but I got a behind the scenes peek at things. I suspect that this product may well be worth a second look in the coming months.

Presentations
Again, my socks remained firmly on my legs. Okay, so they were imaginary socks---the conference was in Los Angeles and it was too warm to wear such things---but let's just go with the metaphor here. The presentations I attended were focused on sharing how a particular school or program was using data. The common thread among all these was that no one starts with a question---and I find this worrisome.

https://twitter.com/science_goddess/status/444960747974455296

While it's good practice for student assessment to guide the next steps in teacher instruction, it is impossible to use every single piece of data derived in the classroom. We have to focus---we need to be picky about where we dig. I know it isn't as simple as that. The hardest part of any analysis is that very first step: Asking a good question---the one most worth asking.

https://twitter.com/science_goddess/status/444955206690684928

I was pleased to hear one presenter talk about how too many teachers see the purpose of data as sorting and selecting. I became worried, however, when she mentioned how "all the data is spewed across the wall in the War Room." My colleague often says that we have to get beyond admiring the problem. Data can be used in strategic ways, to be sure, but that means respecting what we collect and being thoughtful about how we move forward with it. There is something troublesome, for me, in any terminology that involves spewing and war.

As for me, my presentation went well enough---I even ran short, although no one complained about getting away early. :)  The next morning, this happened (well, after the earthquake):

https://twitter.com/science_goddess/status/445559513274273792

This data woman hopes to see you at the 70th annual ASCD conference in Houston next year!

When a Young Woman's Fancy Turns to Thoughts of Excel

You might remember my Shaggy Dog story this winter about efforts to get an interactive workbook online. Even after I found a solution and did my happy dance, the project got shelved...with the caveat that we could pursue something in the spring. Well, it's spring and we're no closer to being allowed to put the project online; however, this may have been a good thing, because it has given me an opportunity to completely revisit things and derive a more elegant solution.

A couple of weeks ago, David Napoli shared this link to a Metro Style Dashboard by Erik Svensen. And while I couldn't directly use the information (I don't have Excel 2013 yet), it did get me thinking about how to update the look of my project---and more importantly, it let me know that slicers work with the Excel Web App.

Slicers, you ask? If you're new to Excel...or if you are still using a pre-2010 version...you might not have seen these. They go along with pivot tables (a piece of Excel magic that we haven't talked about in this space) to "slice" (filter) your data set into all sorts of views. It's probably easier to show you, so my example is below. It uses the same spreadsheet as the Shaggy Dog.

Start by highlighting the first cell in a table, then from the Insert tab, choose Pivot Table. When prompted, I added the pivot table to a new worksheet. This is the easiest way to wrangle your table, because it will be a dynamic item. Depending upon what you want to look at, the items showing in the table will change.

On the left is my pivot table...to the right of it is a dialog box I can use to build the pivot table. Notice that the headings from my original table (Unit, Category, Title, Description) are in the list of fields. I picked the first three for this project. Excel automatically placed them as rows, but I could drag these into any arrangement or hierarchy that I want.

I should share that this pivot table is unusual in that there are no numbers associated with it. Most people use pivot tables to look at the results of different groups...and this is very handy for teachers looking at student data, too. But for this project, I only have text. We'll talk about building pivot tables for your other data in another post.

Okay, now I can add Slicers. If your pivot table is highlighted, you can insert slicers from the Options tab on the ribbon.

Otherwise, just go to the Insert tab and choose "Slicer." You'll get a dialog box---just like the fields from the pivot table---to choose from. I picked the first two options this time, so here is what Excel gave me:

Slicers are like remote controls for your pivot tables. By clicking on any combination of buttons in the Slicers, you can call up any combination of data from the table. They also can move around in the workbook, meaning we can add them to a reporting tool. The defaults on the Slicers, like most things in Excel, are not very sexy. But, fear not, there are a lot of ways you can dress them up.

Now, I can build a report. I'm not going into the full details in this post, but you can download the workbook if you want to see the innards. With the exception of the pivot table and the interface you see below, it's all the same data set and formulas as the Shaggy Dog. I moved my Slicers and built a simple display.

 

The slicers are on top, in a two-by-two arrangement. Depending on the buttons selected, the list of items changes. Better yet, you can upload this workbook to a SkyDrive account and put it on the web. Like this:

You can click the "View Full Size Workbook" button in the lower right corner of the window if you don't want to deal with the scrolling. (When you embed workbooks, you can change the size that shows, but we have some other design constraints here based on my blog design.)

This is a very simple application, but you can certainly take it a lot further. The one I (re)built for work has a block of color to show the user how many resources are available. If you used a tool like this to get a view of what was happening across classes (or classrooms), you could also build it out with a variety of charts or other visuals.

It's spring. Get out and play!

Ain't Misbehavin'

In the last post, we looked at one way to display information about non-academic behaviors in the classroom---things like work ethic, participation, and attendance. These are student attributes that we value and want to report on, but need to be separated from grades and other measures of learning.

Even though I am sure that I will revisit these ideas and fine tune the data displays over time, I thought I would lift the curtain to reveal the workbook innards. No doubt some of you would like to play, too.

Before we jump into the guts, I want to say that all of the attributes and scales are just examples. We could have a lively discussion about which attributes to measure, how you (or your students) could collect these data, and what the cut scores should be in order to be in the range of performance we'd like to see. These are critical pieces to consider...ones that I hope you ponder and kick around with your colleagues and students. I am going to set them aside in this post and strictly focus on the nuts and bolts.

 

On the Report Sheet, I set up a data validation for the students last names. I started by selecting the range of last names on the Attendance/Grade worksheet. I named the range "LastName." Then, on the Report worksheet in the cell where I wanted the dropdown to appear, I chose Data Validation on the Data tab. In the dialogue box, I selected List from the "Allow" dropdown and then in the "Source" box, I added a formula using my named range. For the "First Name" area on the Report worksheet and the selections at the bottom of each of the other worksheets, I used our old friend, the INDEX/MATCH formula.

Let's move on to a few new tricks. Most teachers keep attendance records by marking who is absent---not who is present. But on my graph, I wanted to chart attendance as a positive measure. There are a few ways to do this. The simplest would be to change the way we keep attendance. Mark students when they're present and just count up the number of days at the end of the grading period. I didn't take the easy way out. Instead, I left each "A" for Absent just where it fell. To get the total number of days present, I used this formula:  =30-(COUNTIF(C6:AF6,"A")). In other words, count the A's in a particular row (in this example, row 6 for Cathy Andrews) and subtract that number from the number of available days in the grading period (30). However, this swap won't work for creating the graphs. I need numbers to work with---not text. But a simple IF statement makes short work of that: =IF(C18=0,1,0), where "C18" is the cell from the INDEX/MATCH result for a particular student.

I also used a nested IF to have Excel convert the number of days attended (or points earned) into a level of performance. For example, here is the one that goes with the worksheets for Work Ethic, Participation, and Behavior:  =IF(AG6<=59,1,IF(AG6<=79,2,IF(AG6<=99,3,IF(AG6>=100,4))))
It looks worse than it really is. (Don't most things?) Notice the repeating "AG6," which is the cell with the total number of points I want Excel to use as a comparison. In the first part of the statement, we tell Excel that if that number is less than or equal to 59, to put a 1 in the cell. If not, Excel moves on to the next part of the statement. Even though we know that "30" is less than or equal to all of the cutoffs in the equation, Excel considers only one at a time.

Shall we talk about the graphs? I used the stacked bar from the charts menu. No doubt some of you out there will want to use a regular bar chart. That is appropriate, too. I picked the stacked bar for two reasons. One was that I wanted to do more than show a comparison. I wanted to illustrate a proportion. A viewer can certainly make that inference with a regular bar chart, but it's a little less work with the stacked bar. My second reason was purely about the aesthetics. I knew I was going to want a line graph in each of the four areas (attendance, participation, work ethic, behavior). The vertical real estate claimed by a bar chart threw off the look of things.

Anyhoo, after you select your data for the graph, don't fall apart of it looks like this:

Simply hit the "Switch Row and Column" option and you will see the stacked bar. After that, you can clean up the graph.

I won't go into detail with the line graph. What I will share is that it does take time to create the labels, standardize the sizes, and get everything in place. Take your time. Don't assume that Excel knows best. It's important to make things look good---otherwise, you are missing your chance to truly communicate with the data.

The radar/star/spider chart is found in the "Other Charts" menu. Again, this chart is not everyone's cup of tea. One of the ed research articles I read mentioned using the chart with college students as a form of feedback---and they hated it. This seemed to be due to the lack of familiarity with reading these types of graphs. We see line graphs and bar charts all the time. Radar charts? Not so much. There are other reasons not to choose this type of chart. For example, it works better with cyclical data. And, the area of the graph becomes distorted with the scale. For every point increase of the scale, the size of the area becomes squared.

I would suggest that if you select this type of chart to use with students (or parents) that you spend some time talking about how to read them. Show them how to look at the connections between the spokes and the area covered by the graph. Why did I choose it, given the difficulty in interpretation? Because the attributes of student performance are not simply about comparing them. There needs to be a broader view of what is happening in a classroom---how is the student performing in light of the overall goals...and more importantly, how might non-academic factors be affecting one another (as well as student learning)? I think that you lose some of these capabilities with a clustered bar graph.

What would you do differently with this workbook and these graphs? Would you choose to put the line graphs all on one chart for easier comparison? Do you like the idea of bar charts better than a stacked bar...and clustered columns instead of the radar graph? How would you organize the workbook?

Oh, Behave!

There are two broad categories of grading practices in the great wide world these days. One is termed "hodgepodge," referring to the mixing of student learning and behaviors (e.g., work ethic, attendance...) into a single score. The standards-based version of grading does not crunch scores for learning with student behaviors. There are variations within each category, of course. Even though this blog has modeled various ways to look at student data within a standards-based context, we've never considered options for visualizing student behavior. Time to change that.

I've built a model workbook that tracks four types of non-academic factors: attendance, work ethic, participation, and behavior. Each of the worksheets looks a lot like the gradebook. Names are on the left, but there is a series of dates across the top. There are six weeks (30 days) in the model.

Yeah, Baby

And, there's a reporting page which pulls information from each of the four worksheets. A data-validation dropdown menu for the "Last Name" automagically updates the sheet for each student. Here is what I'm thinking about for the display:

The top graph shows proportions. Attendance displays the number of days, the others the ratio of points earned. For the purposes of this tool, I've assumed that a four-point scale is available for each factor (work ethic, participation, behavior) each day. No points were assigned on days when the students were absent, as it is not possible to observe student behaviors when you aren't in the same room. The line graph below each stacked bar provides a sense of the 30-day trend. Gaps in the line represent days when the student was absent. Whatever was happening with Lucy here, looks like she had a real low point about mid-way through the grading period, but was starting to get back on track with her work ethic and participation by the end.

But I thought it might be interesting to consider the interaction between these factors...and a student's grade. So, the bottom of the report has a radar/spider/star chart. Most people are not fans of this type of graph. I'm a bit ambivalent about them, too. However, student achievement is not just about comparing categories, it's about looking for the confluence of all parts of learning to make decisions about instruction. To make the chart, I converted the total number of points earned in each category into a 4-point scale. I made up a summary grade.

So, here is Lucy's chart:

Lucy Van Pelt

Hmm. Lucy seems to have pretty good attendance and work ethic...but her grade isn't reflecting those strengths. Is her behavior in class keeping her from learning?

What about Dick Tracy, who appears to be living up to his name? Ahem.

Your name wouldn't happen to be "Dick," would it?

This looks like a student who could make a teacher pull his/her hair out. They show up nearly every day and act like a terror when they're there. What do we do about a problem like Dick Tracy? How do we channel his energy into something more productive?

For a final example, here's Flash Gordon.

Flash, ah-ah!

Good attendance, behavior, and work ethic...participation isn't great, but is happening. What's the deal with his grade?

In the next post, I'll lift the curtain and talk about some of formulas behind this beast, but if you're ready to play, you can download the workbook here. Let me know what you think about the graphs. Too much or too little information? Other ideas for the display?

Bonus Round
I've tried to give each student a story with the data. If you're looking for something to start some PLC conversations, you can also use the workbook that way.

Generating Reports from a Gradebook

Back in November, the following comment was left for me:

I have a question, though. The current gradebook has one sheet for the dashboard, and it is possible to flick through the various students one by one. What about if I wanted to be able to see the dashboards for many students? I like the idea of having a single dashboard so that I can keep customizing the report as needed, but is there a way of then exporting this to generate 30 individual sheets for the different students in my class? In other words, hit PRINT once instead of 30 times...

Fabulous question, of course. I didn't know the answer to it at the time, but have had it on my "to do" list. Then, Debra Dalgleish shared this fabulous post last week about how to Filter Excel Data on Multiple Sheets. Although the information wasn't exactly what I was needing, it showed me that finding an answer for the commenter was possible. I just had to push a little further. A little Googling led me to this post on Using Excel 2007 for Progress Tracking in the Classroom. Huzzah! The perfect model to poach.

I copied the VBA from the Progress Tracking post into my Beginner's Gradebook, and then changed it only enough to suit my own named cells/ranges. Now, I will tell you that what I know about VBA would fit in a thimble. I'm eager to learn, but my lessons are currently being driven by "What do I need to know right now?" rather than any sort of organized manner. If you have some expertise you are willing to share to tweak the code to make things work more smoothly, I welcome any and all suggestions.

Here's how things are currently set up. In the "Scores" worksheet, I have added a data validation list (in grey box) that has the last names of the students. This cell was assigned a name. There is also a "Run Reports" button associated with the VBA shown below. In the "Report" worksheet, I had to make a couple of changes. First, I removed the data validation list associated with the last name. Why the data validation works in the "Scores" worksheet, but not the "Report" worksheet when the VBA runs is a mystery to me. Beyond that, I had to name the cell in the report with the last name, and use a formula to make it equal to what I named the cell with the new data validation list.

I'll put the VBA  code below, in case anyone has any suggestions. With very few exceptions, it is identical to the one in the Progress Tracking post by Danny Khen---definitely not my work. But for now, feel free to download the workbook and give things a try. It is a macro-enabled workbook, so be sure to tell Excel it's okay to play with. When you click on the "Run Report" button, a new workbook will open and populate with a separate report for every student, all on their own worksheets. All of the data will be in their places with bright shiny faces. It's magic! (Where's Doug Henning when you need him?) All you need to do is tell Excel to print the entire workbook and you're good to go.

I've Got (Graphs for) the Power (Law)

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.

Happy dancing!




Music clips from The Power by Snap! (c)1990.

Report Card Redesign

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.

What do you see that you like?