HW: Intro to working with Data

Maeve Kane

This will be easier if you read the two assigned readings first!

Edited to add: #ERR and #REF errors are errors–they mean that your function is mistyped or your range is incorrect.  #NUM errors are ok in median cells–it just means you’re trying to find the median of zero, which is nothing.

  1. Copy the “original” spreadsheet into a new sheet in drive.google.com. Call your first sheet original
  2. (if you need to stop and come back to it later, you can access your sheet by logging into your google account and finding it in drive.google.com)
  3. Create 7 new sheets in your workbook and title them white-only households, only white men, only white women, households with slaves, only “other free persons”, only non-slaves
  4. Use the filter function to display only the households in each category (hint: use the empty/not-empty/none options). For example, to select only households with slaves, you would need to filter “slaves” to not-empty. To select only households of “other free persons,” you would need to filter all other categories to empty and “other free persons” to NONE.
  5. When you have each category filtered, click the intersection of the column and row labels to select all, or use CTRL+A to select all. Copy and paste your filtered households into the relevant sheet.
  6. Repeat for other categories
  7. On your original sheet, SUM the number of people in each household in the blue column to the far right. You MUST do this with a function—don’t enter the number by hand! Remember that when you type your function you can select your range by dragging, or you can type in your range: ie, SUM(A2:G2) will add up all numbers from column A cell 2 to column G cell 2.
  8. Copy your functions by selecting and dragging where you want them to copy.
  9. Don’t forget to SUM all households and find the MEDIAN household size at the bottom of the column!
  10. At the bottom of the original sheet, do the same for each category of people: find the SUM, the MEDIAN, and the COUNT.
  11. Do steps 6-9 on each of your filtered category sheets – make sure to make row and column labels for each since you didn’t copy and paste those!
  12. Create one more sheet and name it medians. Copy the template in the example workbook here
  13. The blue cells should be filled with functions. DO NOT HAND ENTER NUMBERS. For each household type and category, write a function to find the median of each person-type in each household type. For example, to find the median of “free white males under 16” in all households, you would write =median(total!B2:D761). B2:D761 is your range of cells, and your sheet name is separated from your cells by an exclamation mark. You can also type your function, ie, =median(, and after you type the parenthases, click on the sheet you wish to select from and select your range of cells by dragging.
  14. Note that the medians for the free white categories male and female should include more than one column from the original sheet! Be careful with your ranges!
  15. Once you have all your medians written in the median sheet, select all on your median sheet, and go to insert>chart
  16. In the Chart Types tab, make sure that “Aggregate Column A,” “Use row 1 as headers” and “Use column A as labels” are selected.
  17. Hit Insert chart, send Dr. Kane the workbook using the Share link in the upper right (mkane2@email.com)