Mayflower by the Numbers:

An Activity for Spreadsheet and Internet

Introduction:

Have you ever visited a historic cemetery?  Wandering among the headstones, you may have seen that each marker usually contains the individual's date or year of birth and the date or year of death.  Sometimes you may see a headstone for a husband and wife, and sometimes you may find a family plot.  However, apart from these clues, you have very little information about these individuals and how they may have lived their lives.

Even with just this little bit of information, we still can learn something about the inhabitants of an area, especially if their lives are tied to a historic event, such as the settlement of Plymouth Colony in 1620.  We may not be able to see the headstones for the colonists who came over on the Mayflower, but for many of them we do have the year that they were born and the year that they died.  Using a spreadsheet, we can figure out how old they were when they got to Plymouth Rock, and how long they survived. Using some logic, we may be able to figure out something about families, and what happened to the colonists after their arrival.

Activity:

1.  Click on the "Passenger List" link on the left and select "Save" to download the worksheet onto your computer.  Be sure to save the file where you can find it or where your teacher tells you.

2.  Open the Google Spreadsheet from this link.  Go to File, then Make a Copy so that you have your own copy of the spreadsheet to edit.  DO NOT EDIT THE ORIGINAL.

3.  The file contains a partial list of passengers on the Mayflower, along with the year that they were born, and the year that they died.  We want to know how old the passengers were when they died, and how old they were in 1620 when the Mayflower landed.  You will see columns labeled, as seen in the table below:

Last Name

First Name

Year of Birth

Year of Death

Age at Death

Age in 1620

Comments

Alden

John

1599

1687

88

21

 

4.  For most of the passengers, their Age at Death and Age in 1620 columns are blank.  You can use a spreadsheet formula to calculate these.  Each little rectangle in the spreadsheet is called a "cell", and each cell has an address.  For example, in this spreadsheet, the cell address for John Alden's last name is cell A4, because it is in the A column, and the 4th row.

To calculate how old John Alden was when he died, we would subtract the year of his birth from the year of his death ( 1687 - 1599 = 88 years old ).  The cell address for his birth is cell C4, and the cell address for his death is D4.  The formula we would type into cell E4, which shows his age at death, would be "=D4-C4", because we want to subtract the contents of cell C4 from whatever is in cell D4.  After you type the formula, press ENTER to see the result.

To calculate how old John Alden was in 1620, we would subtract the year of his birth from 1620 (1620-1599 = 21 years old ).  The formula we would type in cell F4 to calculate this would be "=1620-C4".

5.  Type in the spreadsheet formulas for the rest of the passengers.  Be sure to use the correct cell addresses for each formula.

Shortcut Hint: You can save a lot of typing by "dragging" the formula down the column.  To do this, click in John Alden's Age at Death cell (E4).  Notice that the cell is now highlighted.  Also notice that there is a tiny plus sign (+) in the lower right corner of the cell.  Move your mouse over the plus sign, click, and drag it down the column.  This will copy your formula so you don't have to re-type it.  You can do the same thing for the Age in 1620 column.

6.  The "Comments" column is a space where you can type any comments or observations you make about the passengers.

7.  When you have finished entering all of the formulas, save your spreadsheet, and you may go the the next section.

Discussion:

Once you have completed the spreadsheet formulas, take a close look at the ages that appear.  What do their ages tell you?  Can you figure out who is in which family?  Who is the father? The mother?  The children?

 

Also look at the year that they died, and the year that the Mayflower landed.  Looking at this information, what can you figure out about the colonists first year at Plymouth Rock?

What other interesting things do you see about the passengers?  Be sure to record interesting observations in the "Comments" section so you can share with your class. In small groups, or with your class, discuss some of the things that you have learned about the Mayflower passengers.

After you have finished discussing your findings, visit Caleb Johnson's Mayflower History webpage by clicking on the link.  You will be able to look up even more information on each of these passengers, and some that were not included on this list.  See if your observations are accurate.

For Teachers:

This activity works best for grades four and up.  It can be completed in one class period, but may be extended over a couple of days depending on the age and ability of the class.

The spreadsheet also contains some hidden information about how many of the colonists perished in the winter of 1621.  At the bottom of the spreadsheet is a blue bar.  With the mouse, highlight the cells covered by the blue bar.  Once this has been highlighted, click on the text color and select white.  This will unhide the number of passengers that did not survive the first winter at Plymouth.

Notes:

All information for this activity is based on Caleb Johnson's Mayflower History website.  Some of the years are estimates, based on conjecture from information on the site.

This activity is designed to work with any recent version of Microsoft Excel.  It can be adapted for use in other spreadsheet programs.

Other Resources: