Who put the higher in hierarchy? I don’t know, but the reality of class and its near-ubiquitous sidekick, the ruling elite(s), have been giving sociologists something to write about for the past 150 years or so. And if the definition-mongers can’t decide what class really means, the very tenacity of the debate has entrenched the term deeply in the discourse.
And if the stereotypes have it even half-right, nowhere does the beguilements of class charm its masses so seductively as in England; and a recent Guardian spreadsheet, its data ported from a study by the Social Mobility and Child Poverty Commission, advances education as a proxy attestation for the imponderable class. Here it is:
The workbook’s two sheets plant their feet on two educational rungs – (pre-university) school and university types – and see how their distributions break across a set of moving-and-shaking professions (which aren’t identical in the sheets, by the way). The samples are small, but the universes from which they’re drawn are transparently described in the Elitist Britain methodology addendum, likewise available at the above link (as is the Methodology – Annex spreadsheet, which actually names the individuals surveyed). And the samples are largely just that – fractions of the smallish universe pools, for example, BBC Executives, clarified by the addendum as “Senior BBC Executives on the BBC website for transparency purposes”. The spreadsheet numbers 187 of the functionaries, but the Profession by school type tab finds actual information for but 93 of them (and 125 on the Professional by higher education sheet).
At least it’s all up-front, and the workbook means to put an evidentiary face on British elitism by tracking the higher achievers on its radar and their schools. And once you get past the perennial column auto-fits and confront the Profession by higher education figures, the point is more-or-less made. 59% of the Cabinet members for which the data obtain, for example, were Oxbridge attendees, with 77% having found their way into a Russell Group school (the Group is a consortium of “better” universities). Still, you’ll find interesting if expectable variation, e.g. the 0% Oxbridge representation among pop stars (defined as “UK artists who had one of the top 40 selling albums of 2010, 2011, 2012 or 2013”), although 27% of these did misspend their youth at a Russell Group school (although it also appears that attendance at, rather than graduation from, is counted here).
Again note the Percentage known data are exactly that, proportions of members of the sample for whom educational data have made themselves available (with the rather curious exception – in both sheets – of Select Committee Chairs, entered as 85, and not .85. Simplest tack, here: replace with .85 and commit to the Percentage format) . And the attending fields purport to convey attendance data in percentage terms, too – but they don’t. They’re rather delivered in whole-number terms, and the 91 in cell E2 in the Profession by higher education sheet, for instance, won’t register as the putative .91 but as a number 100 times greater instead; and if you want to work with numbers in lieu of the percentages – and see say, a 114 in E2 (a rounded 91% of the 125 BBC execs for whom information availed) – well, then, you can’t multiply E2 by C2, because you’ll get 11,375. Now the remedial spreadsheet math isn’t difficult by itself, but it has to be done somewhere; and here’s what I’d do.
In a blank cell – I’m working in J2, for a reason – enter $C2/100 and copy it (yes, I do plan on explaining). Then select E2:H28, the range of to-be-percentaged numbers, and click Paste > Paste Special > Multiply:
And here’s what’s happened in view, and in review, of our intention: Remember we want the 91 – that ostensible percentage – in E2 to culminate in a 114 – which, after all, is 91% of its denominator, the 125 BBC executives for whom university-attendance data are on hand. Thus we want in effect to be able to multiply 125 by .91, and the strategy we’ve blueprinted here makes its first move by dividing the number in C2 – that 125 – by 100; the resulting 1.25 is then to be multiplied by the 91 in E2, realizing that 113.75 for starters. What the Paste Special orchestration does then, is copy and paste $C2/100 atop E2:H28 – but instead of the standard-issue paste, it multiplies every value in the range by its respective C-columned value. Thus the Pop star, Russell-Grouped 27% percent – again, really the number 27 as it first appears –is multiplied by the 72 Stars in C14 for whom the educational information is available, and 72/100*27 gets you 19.44 actual Pop stars – and if you’re wondering about that .44 of a Star, hang on.
Now it’s true – an odd, unsettlingly circular cast tinctures the Paste Special Multiply routine, pasting as it does a formula (and it is a formula – don’t look for pasted values this time) that names the selfsame cell into which it’s pasted, but that’s how it works (as do some formulas imparting conditional formats, by the way), and because it works you’re spared the conventional way around the whole problem: making yourself at home in I2 (in the first unoccupied column at hand) and entering
thereby recalling the aforementioned 113.75, copying the above across I2:L28, and finally aiming a Copy > Paste Special > Values back onto E2:H28, and deleting the now-duplicative I2:L28.
And now for that .44 and kindred decimals. Fact-checkers tend to discourage sentences reporting that “113.75 BBC executives attended some university”, although I can think of quite a few pop stars whose talent quotients fail to rise to integer level. What we need here is a numerically real, as opposed to a format-dappled round-off, and if we want to get there without incurring still more formulaic overhead there’s a venerable, but faintly recognized, option in there waiting for our call.
First, select the now-heavily-decimaled E2:H28 and format the data to zero decimals. But again, that refinement merely streamlines the numbers’ display, and doesn’t streamline their values. But watch this: click File > Options (remember – I’m in the 2010 release) > Advanced > and scroll here:
There it is – Set precision as displayed – one of the only, if not the only, format-motored command that actually re-quantifies the value of a number as per its present onscreen appearance. Format the decimals in 113.75 away until you see 114, turn Set precision as displayed on, and what’s left is a real 114. Multiply it by 2, for example, and you’ll get an authentic, usable, 228, not 227.5.
A last spreadsheet-grammatical point here: as parenthetically observed above, the Paste Special > Multiply script pastes formulas, not hard-coded values, into its targeted cells, e.g.
But this seems to work, too:
And indeed, this very matter happened to have found its way into last week’s post. Any thoughts about those Excel-spawned parentheses?