Thirty Days of Making: 1760 Census Estimate

I’m in Day 10 (one-third done!) of a short series: Thirty Days of Making. Every day for the next thirty days, I intend to make something, anything, that is in some way connected to school. There won’t always be pictures, and I reserve the right to credit myself for things that I help my kids make. But I’ve decided that I need thirty days of maker success and maker failure under my belt to be a better designer.

I’ve decided that artwork counts, but not writing (unless it’s part of the art, like calligraphy). Digital work counts, but it has to be useful or publishable.

Some days there will be pictures, some days there won’t be. Each blog entry will contain a list of some of the materials and tools, a quick review of the success or failure of the Making, and a reflection on what I think I learned from the endeavor.  (My friend Alicia is beginning a new series along these lines, 12 weeks of the Artist’s Way — I wish her well in her process, go check her out!)


Reason for Project: Teaching Spreadsheets

I’m teaching my seventh graders in my Digital Arts and Sciences classes about spreadsheets, which includes a discussion of summary tables.  Learning this stuff is hard.  I learned it all by happenstance, many moons ago, over many months of trial and error.  Now I’m trying to teach it in a comprehensive way — at least the basic stuff like multiplication, division, addition, subtraction, PEMDAS, sums, percentages, and so on.  It turns out to be complicated to teach this stuff, as well as to learn it.  What order should it be taught in? Should you start with baby stuff like SUM and PRODUCT and basic formulae using =, +, -, *, and / and the (parentheses)?  Or should you teach them how to move into SIN() and TAN() fairly early?

I decided I was going to do this backwards forward, and I had them collect a data set. The seventh grade studies American history from colonial times through the Civil War.  So I had them download the US Census data for one state from WIkipedia (1790 Census article, since is closed) and had them develop a collection of percentages.  I did some of this before, but then I showed them how to calculate an estimated population in 1760 (based on an assumption of 4% growth per year from 1760 to 1790).  Then I had them combine the data into a single table (thanks GoogleDocs for Education!), and sort the data (sorta), which I decided to do by color (I’m still figuring out pivot tables; I eventually want to design a pivot table that mines and minds that data by region and sorts and counts it, so that you can show kids how Maryland is sometimes a Southern state and sometimes counted as a Middle Atlantic state — and that how you count changes the statistically-relevant figures).

I want to emphasize that the attached spreadsheet is MY work, not my students’ work.  They were working on their own joint spreadsheet on their own computers; this was the demonstration model they worked from, thrown on a wall via projector. I mined this data from the Wikpedia article for the 1790 census, and then extrapolated the various percentages, and then plugged in the formula to estimate the retrograded population shrinkage from 1790 to 1760 in order to create a rough estimate.  Beyond the 7th grade, this should not be considered official or relevant data.

Process and Result

I’ve already talked about building this spreadsheet in front of the class in the previous section, which I think outlines my process fairly well.  I collected the data before class, and plugged it in; I was then sorting it and processing it using formulae directly in front of them as they watched.  Quick and dirty it is… I wanted to arrange the states in such a way that I could sort the rows alphabetically; and by state, and by region.  Couldn’t do it. Couldn’t remember how, or never learned.  Ooops.  Gotta figure this part out.

The results still made a lot of things clear to students — how slavery was not institutionally common in the North, or even as much as in the Middle Attlantic, as in the South.  But it also showed non-obvious results, like the massive flow of immigration into the North and middle Atlantic states, and the unexpectedly large number of young men in New England.

The spreadsheet looks ‘pretty’, yes, but it’s also capable of revealing things that kids don’t find out using ordinary methods in textbooks.

And here’s the information, in an xlsx file: Colonial Spreadsheet – Sheet1, and .pdf: Colonial Spreadsheet – Sheet1 I was hoping to give you the .csv file too, but it turns out that WordPress doesn’t support that file type for media on a webpage.  Oh well: Encourages hackers or something like that, I expect.  Have a look for yourself.

Reflection on My Learning

I’m appalled at what I don’t know about spreadsheets.

I mean, I know about a half-dozen functions, like =SUM() and =PRODUCT and =POWER, and =DIVIDEND and =AVERAGE and =CONCATENATE [which I once used for building a fascinating little table that calculated the regular verb forms in Latin] and =MEAN.  Actually, that may be most of the functions I know, right there.  But there are phenomenally more complex tables out there which do fascinating and amazing things.  I think the most important thing I learned from this project is how much I don’t know about a subject I’m supposed to be teaching.

But part of what I get from this project is that we learn to become proficient with the tools that we use most often.  What we practice, we perfect.  The child psychologist Ned Hallowell explained this to me in his book the Childhood Roots of Adult Happinesswhen he suggested that the the second thing we have to teach children (or ourselves) to do when learning a new subject is to play.  But the first is to connect the kid (or ourselves) to the subject to be learned.  Mastery happens at the end of the process, not the beginning.  Our goal as middle school teachers is to connect the kids to subjects — whether it be house-building or statistics or sewing or public speaking — and then help them learn to play with that information.  Too often we jump right past play to the practice.  And we lose kids along the way.

Well, not so here.  For a kid to pick up a hand-powered crank drill, and point it at a kid from twelve feet away, and pretend it’s a weapon (do all ad hoc blaster pistols go “pew! pew! pew pew pew!”?)  should be a clear sign to anyone and everyone that this kid has no idea what the tool is.  He’s not being aggressive, necessarily — he just can’t imagine what such a tool is for, because he’s never seen one in use.  For the knowledgable, informed child, a quite different result emerges: things with holes in them.  She has newfound power, because now she knows how holes are made — she’s done it.  Obviously, you can think about changing the world with tools you’ve used; you can’t change the world if it requires tools you haven’t used, unless you can imagine and then build those tools.

As an aside to the wise — this is why we’re directed to work with the seven metals so much, and the herbs, and the stones, and the alembic and athanor of the alchemist so much.   The hands-on work is absolutely critical for the re-formation of the mind, and the rooting of personal experience in things and processes rather than book learning alone.  We can astralize all our learning, or rely on the spirits alone… but some of what we do must be done in material realms.

Reflection on General Learning

From a more general computing vs. making angle,  I think this was more making than computing. First, it was (fearlessly) done in front of an audience. The result was that I did it only with the formulae I was most familiar with, rather than with fancy number-play and crazy experiments in Function-use.  Could it be better-designed? Yes, of course.  But now that the data exists (and is downloadable and exportable) maybe someone other than me will do something else with the data than I could.  From another, asking kids to copy exactly what I did was … not nice.  They built the structures I had built, but they hadn’t learned how they worked, for the most part.  Maybe some of this has to be learned by methods that are different than I did, or that function more cleanly than this.  But it’s hard to learn efficiency, sometimes, until you’ve done it the crazy way


Two out of Five.  It’s not awesome.  It could be better.  It could have pivot tables and dynamic systems for calculating and working with data.  IT doesn’t, though.  Oh well.  Better luck next time.

Liked it? Take a second to support Andrew on Patreon!
Become a patron at Patreon!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.