dcps

This is the latest in lame GSheets hacks I put together for Kelly.

Every Monday, she hands out progress reports. And every Monday, two manners of delusion ensue:

1. The kids who are doing poorly assume that they’re doing no worse than anybody else (the system must be biased!)
2. The kids who were doing well but have been slipping assume that they must still be at the top of the heap, because they’re the smart kids so of course they are.

Wouldn’t it be nice if you could point to a classwide grade distribution to show where each student fits in the grand scheme of things?

Indeed it would.

Enter, the grade distribution chart.

It’s driven by this GSheet, which simply takes grade reports from Aspen and turns them into stacked dot charts that can be printed and stapled to your data wall (remember data walls?). You can’t see it in the sample, but there is also a dot at the class average.

It’s anonymous, but as long as the students have their progress reports (which shouldn’t be a problem given that this is generated from them), the kids can see exactly where their grade stacks up against their peers.

The biggest utility, though, is for classes who remain convinced that they’re “the smart ones” and thus must always be at the top of the heap. When they inevitably try to rest on their laurels, it’s always satisfying to show them getting blown away by the “earnest plodders” in the remedial group. 🙂

So, how do you use this beauty?

Two sections follow: initial setup and regular use.

Initial Setup

1. Open the template GSheet. Make it your own by clicking File | Make a Copy.
2. Rename the tabs to match your section numbers. Stick with the existing syntax since the other sheets are designed to expect an A-B section number syntax.
   • If you need more than 5 sections, just click on one of the tabs and choose Duplicate. Remember, though, that you need to do this 2 times: once for the Raw sheet and once for the Chart sheet.
   • Further, you’ll have to direct the Chart sheet to the appropriate columns in the Data sheet. In short, you may need to get in touch.
3. Go to the Data sheet and change the titles, making sure to retain the format of Course A-B with section numbers.
4. Update the titles of each chart sheet appropriately (alas, there’s no easy way to make them auto-update).

Regular Use

1. Open Aspen and navigate to the Gradebook for a particular section.
2. Load the Scores page.
3. Run the Assignment History report.
4. Set the format to CSV and accept the warning.
5. Open the report file in Excel.
6. Click the top-left chiclet (the gray square between the headings for Column A and Row 1)
7. Copy to the clipboard.
8. In the GSheet, open the appropriate Raw tab.
9. Click the same top-left chiclet to select everything.
10. Hit Delete to clear out the old data.
11. Click Edit | Paste Special | Paste Values Only to paste the data.
12. Repeat for the remaining sections.
13. To Print, open the appropriate Chart tab.
14. Hit Ctrl-P to get to the Print Preview dialog. The charts are formatted in Landscape for improved visibility.
15. Click Next to get to the Print screen.
16. Print per usual.