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?