All due props to Josh Boldt, fighting the good fight for adjunct college instructors worldwide. It’s a dirty job, and Boldt decided he’s gotta do it.
You know these adjunct folks well. After all, you probably owe more than half your college credits to them, those front-line staff with flat-line salaries, keepers of the peace in anarchic lecture halls teeming with text-addled, twittered-out undergrads with anything but today’s assignment on their mind. Take it from me, I’ve been there; and just because the words “adjunct” and “tenure-track” never appear in the same sentence is no reason not accord adjuncts the full eye contact they deserve when you catch them thumbing through What Color Is Your Parachute? in the Barnes and Noble.
What Boldt, a writing teacher and freelance writer in Athens (Georgia), has done and continues to do is accrete a dynamic record of the adjunct experience via a pair of spreadsheets to which adjuncts are asked to volunteer basic workplace information on a form. Each response – comprising data such as school name and location, department, salary, contract duration, etc. – is meant to add a few pixels to the big picture of the adjunct circa 2012, which may not be terribly pretty. As Boldt puts in on his site:
This website was designed collaboratively by the new majority of motivated, intelligent, and driven academics who are struggling to use their experience and knowledge in a meaningful way that benefits both themselves and society.
The spreadsheets, one earmarked for adjuncts in the US, the other for international part-timers , might thus have some interesting things to say about this oft-bedraggled work force. You can download the US rendition in Excel form here:
(Note: While you could in principle grab the spreadsheet from the Adjunct site via an Excel web query – a capability we’ve yet to broach in this blog – my repeated tries at reeling the data in through via this route failed, for reasons not entirely clear to me. But no matter – web queries refresh their data at identified intervals, normally a cool and desirable thing. But the refresh shakes out any changes made to the base spreadsheet, restoring it to its primeval state, and that’s not what we want in any event.)
I’ve saved the sheet precisely as it looked when alighted onto my blank workbook, and as such seems to be in need of a variety of repairs:
So let’s put on the hard hat and get to work. First, if we want to put the sheet in the service of additional analysis, e.g., through a pivot table etc.., we need to delete rows 3 and 4 – they’re blank, and as strand the header row from the data beneath.
Second – and I originally missed this – we need to delete the A column; look closely and you’ll see dots or periods speckling the A cells. These are data of the most purposeless kind, and should be chased away.
Next – the header row, along with many if not all the data rows, has clearly been subjected to the Wrap Text option, a fillip to which Excel isn’t doing much justice (see above). Rather than refit individual columns to widths that would suitably befit the wrap typography, what I’d simply do is select the entire worksheet (here you should click the Select All button, that unassuming blank button to the immediate left of the A column header) and click the Wrap Text button (Home tab, Alignment button group) which is currently illuminated. By turning the effect off all the data straighten themselves out atop the floor of their cells, even as the header labels pierce the columns to their right. Note that the worksheet remains selected, and so you need only double-click any column boundary and institute a mass auto-fit across the data (true, some columns will dilate to an untenable width; these can be cinched manually). Note the data cells entries are middle-aligned – that is, they ascend to the vertical centers of their cells – but I’m not bothered by any of that, and I’d leave that effect alone.
Now we need to clear away another data obstruction. Each state’s data is topped by what could be properly regarded as a sub-heading, in which only the state’s name occupies the row. These geographical identifiers can’t properly be termed data, as they bear no information about any institution and simply subsume the records immediately below. I tried a number of quick workarounds of the sort-these-rows-to-the-bottom variety we’ve discussed in earlier posts, but because the pertinent cells are so sporadically populated I had to turn to Plan B. In the first free column – K, by my reckoning – enter a header in K2 (it could be anything – I simply called it Sort), and write this formula in K3:
=IF(COUNTA(A3:J3)=1,”zz”,”aa”)
This expression does the following: it counts the number of cells sporting any sort of data in the named range (COUNT, on the other hand, tallies only those cells containing numeric values). If the count evaluates to 1, you’ve apparently nailed a row containing a state name only. If it is 1, the formula enters the text zz in its cell; otherwise, it returns the letters aa.
Then copy the formula and copy it to the remainder of the records – only here, because so many blank cells have presumed upon the data, click in the name box (the white field to the left of the Formula Bar that typically reports the current address of the cell pointer) and enter K3:K1984 (1984 happens to be the last data row on the sheet). Tap Enter twice (the first tap selects the range; the second actually does the pasting). Then sort A to Z on column K. And guess what? You’re rudely interrupted by the “This operation requires the merged cells to be identically sized” error message, about which something obviously needs to be done. Presumably because merged cells absorb two or more cells, the sort command gets flummoxed by the resulting cell address conflations and can’t do its thing. Now, I don’t particularly know which cells are in actuality the merged ones, but I’ll just select the entire worksheet again, click the down arrow fused to the Merge & Center button (Home tab, Alignment button group), and select Unmerge Cells (“unmerge”, and its cousin “unhide”, rank as two of the most stilted verbs in the lexicon, and people at Microsoft get paid a lot more than adjuncts to come up with stuff like this). Now you can sort A to Z on K, and insert a blank row right above 1928, which is where my state labels now start (remember that state abbreviations are supplied in any case in the B column if you need them).
All of which raises the question I was afraid you’d ask: Now what? And that’s precisely the point. Take a look at the Pay Per Course field, likely to propose itself as the essential parameter in the worksheet. But the data here are so multifarious, so apple-and-oranged, that our next analytical move seems to have been checked – if not check-mated.
What to do? I’m not sure. While many of the data are standard numeric fare, many are not, and banishing the latter from the worksheet will comprise the data too dearly. Would it be possible to recondition those non-numerics into quantitative form? I wouldn’t declare the chore impossible, but you’d have to bounce some headachy cost-benefit equations off your medulla, and think hard about how you’d make over a cell like this:
There are three salary figures in that cell – informative, to be sure, but try to add them.
When the data don’t cooperate –when they exhibit snarly recalcitrance – you may have little option but to direct your attention to a more compliant workbook –in other words, break out the white flag, cry Uncle, and punt (is that what they mean by a mixed metaphor?). (You might also ask yourself, by the way, how you would have constructed the worksheet from scratch had you been entrusted with that assignment.)
But none of this should be read as an arraignment of what Josh Boldt is trying to do. He’s putting together a public record, one answerable to his intentions, not ours. That the worksheet isn’t all that analyst-friendly is a discontent I’ve superimposed on the data. The sheet is what it is. And were I still an adjunct I might dial into his site and add my own info, too.
But I need work on a more pressing issue now. I need to find out if people still say “props”.
@abbottkatz
spreadsheetjournalism 
Leave a Reply