CEMETERY DATA ANALYSIS USING

MICROSOFT EXCEL

 


NOTE: YOU WILL BE USING THESE DIRECTIONS FOR ANALYZING A DATABASE WITH OVER 800 ENTRIES. PLEASE FOLLOW THE DIRECTIONS EXACTLY AS WRITTEN

If you have trouble, small movie clips have been attached to this page to show what you should do.

Microsoft Office and/or Microsoft Excel are on all machines in the public computer labs on campus. You can use either a PC or a Mac to complete this lab.

INTRODUCTION
Begin the program by clicking the Excel icon (similar to the one below) which should be located in the applications folder of all machines in the publick computer labs on campus.

The program will launch and a HUGE spreadsheet will appear.

Try clicking on several empty cells and watch the window on the lower left side of the status bar at the top of the screen. Each time you click, the "address" of that cell should appear.

If a column is not big enough for your entries (Names, inscriptions, etc.) You can make the column larger by pointing the cursor on the line between the columns where the letters are located AT THE TOP OF THE PAGE--IN THE GREY ZONE. When your cursor is over the line, it will change to a cross. Push down the button on the mouse and slid the line over to make more room. You can only make the cells longer by increasing the column width. The cell height works in a similar manner.

 

Redtop Cemetery Exercise

You can easily open the Redtop excel data file by using one of two methods.

If it won't open, then open the excel program and go to File-->Open.

Find the Redtop file you downloaded and open. Proceed to Data Manipulation section.


I. Data Manipulation and Analysis--Determining age at death

Excel can do many different types of calculations, such as determine the age of the person when they died.

To calculate age data:

  1. .Click on the first data cell in the Age of Death Column (O8). The cell should be highlighted and its address should be in the window.
  2. .Excel knows when a calculation is to be made by using the = symbol. So, type a = symbol after highlighting the cell and you should see it appear on the line to the right of the cell address.
  3. Next, you need to tell the program what calculations need to be done. To determine the age of death you need to subtract the birth year from the death year. So, enter the first death date address (J8) and then a minus sign -
    followed by the birth date address (G8). Thus the line should now read =J8-G8
  4. Click the "return" key and the age the person died should be displayed.
  5. What about the other cells? Don't panic, you don't have to do this each time. Simply highlight the age at death cell that you just created and point the cursor "cross" at the tiny little square on the lower right side of the highlighted box.
  6. Click the mouse button and "drag" the mouse downward through all the cells that you want ages calculated. When you release the mouse button, the age of death for each of your people should magically appear! (Essentially, so have just "copied" the formula from cell O8 into the other cells in column O.)

    Confused? Click on the help button below to watch a quicktime movie showing you what to do.


II. Determining Composite Data

  1. Label the cells in the top row of your spreadsheet with the following:
A1 Count
B1 Mean Age
C1 Max Age
D1 Min Age
E1 Min DOB
F1 Max DOB
G1 Min DOD
H1 Max DOD

Count is the total number of graves used in your study
Mean Age is the average age at death
Max Age is the oldest individual
Min Age is the youngest individual
Max DOB is the latest date of birth in your study
Min DOB is the earliest date of birth in your study
Min DOD is the earliest date of death
Max DOD is the latest date of death

In the cell below "count", type the following:

=count(range of cell addresses for data)

for example =count(B8:B68) if your dataset has 60 graves. Hit return when finished and you should see a number representing the number of graves you used in your study.

In the cell below "Mean Age", type =average(the range of your age cells) and hit return

Using the same method to indicate the range of cells, the following information will help you complete the rest of the calculations.

count =count
mean =average
min =min
max =max


Sorting data by sex

  1. Highlight your data set (starting with A8, B8, C8, and so on) by pointing the cursor on the first entry and dragging it diagonally across the entire data field. Do not include any text information or earlier calculated count and min, max information. Highlight only the data in rows 6 and below.
  2. On the status bar at the top of the page is the DATA menu. By pointing and clicking on "Data" a menu drops down. Select SORT.
  3. A "Sort by" window will appear. Select the column with sex information in it and sort the data in ascending order. Click ok. Your data should be sorted so males (1) will be first on your listing and females (2) will all be listed second.
  4. Highlight the cells in the last row of males. Don't highlight the entire row, just the cells with data (probably through column O). Place a "border" line along the bottom of these cells. Different versions of Excel will have different ways to accomplish this. If you are using the Mac Version of Excel (Office 2000) just click on the border icon at the top of the page. Slide your cursor slowly over the icon to find the right one. A tiny arrow to the right of the borders icon will allow you to select different types of borders (including just a line!) Ask for help if you can't figure it out. This visual separation between the males and the females will help out later.


Description of data by sex


Use the count, max and min procedures two more times: once using the group of females and once using the group of males. Record this information in cells at the top of your database along with the total composite data.


Preparing a Histogram
In order to make a histogram you must first organize the data.
1.Select a new column and label it Age Class. This column will contain the age classes which will appear on the y axis of your table.
2.Divide your Age at Death data into five-year intervals in the Age Class column in ascending order. Begin with 0, 5, 10, 15, etc... and end with an age that is greater than or equal to the Max Age. These numbers will translate into age classes of 0, 1-5, 6-10, 11-15, and so on. Thus if the oldest person is 101, go up to 105. Enter these numbers (0, 5, 10, 15, 20, etc.) in the Age Class Column.
3.For example, if the max age at death in my cemetery section is 17 (highly unlikely), then my Age Class column (column "R") will have the
following numbers in the indicated cells:
R4 will have the value 0
R5 will have the value 5
R6 will have the value 10
R7 will have the value 15
R8 will have the value 20


When finished, click on TOOLS in the header bar and select DATA ANALYSIS. NOTE: If "Data Analysis" isn't in the list of
choices, select "Add In's" instead. A box with lots of choices will appear. Check the box next to "Analysis ToolPak". Click
ok. The box will disappear and when you now select "tools" the choice Data Analysis should now appear at the bottom of the
list.
4. In the window that appears, you must input the Age Data into the input range and the Age Class into the Bin Range. The easiest way to do this is to highlight the column by clicking the cursor on the top cell and dragging it down to the end of the data in that column and releasing the mouse button. This will automatically enter the data for you. If that doesn't seem to work, you can enter the data by typing the first and last cell address (for example, T8:T800).
5. Select New workbook for your output. This will place your histogram on a new page.
6. Check the box that says chart output.
7. Click OK
A table and a histogram should now be generated. If the output is not what you want. Just delete the chart by clicking on it and hitting the delete button and start over again. Do the same with the data created.


You can resize your chart by clicking on it. Small little black squares will appear on the sides of the box. Just place the cursor on these boxes, push down and hold the mouse button and drag the chart larger or smaller.


You can also change labels on your histogram by double clicking on any of the parts. For example, if you want to change the Bin label to say "Age of Death", just double click on Bin and a box will appear which allows you to change fonts, patterns, alignments etc. If all you want to do is change the text, just highlight it by clicking and type your new text in its place.
You can also change the units on the X or Y axis by double clicking on the axis. A box will appear- select scale and enter the unit division you want. (In this case, I think units of 1 are appropriate.)
You can easily copy and paste this chart into any word processing program. Just click on it and copy. Then move to the word processing program and paste.


Preparing your data for a Population Pyramid


1.Create 2 more columns next to the Age Class column. These should be called: Number of Males and Number of Females. Next you'll do 2 more sorts.
2.Select the entire set of data for the males. From the Data menu, choose Sort and then sort by the column containing age at death in ascending order. Do the same sort for the females.
3.Using your Age Classes as a guide, visually count how many females "fall" into each age class. For example, if your first 6 death ages are 0, 1, 5, 7, 8, and 8, then you have 1 person in the "0" class, 2 in the 1-5 class, and 3 in the 6-10 class. Enter this data in the Number of Females column.
4.Repeat the above step for the males, using the column called Number of Males. One thing more need to be done for this column: type a negative (-) sign in front of each number in the Number of Males column. This will separate the males from the females in the population pyramid.


========= Now you are ready to create your graph using the Chart Wizard.


Making a Population Pyramid of Death Ages

NOTE: There are slight differences between the Macintosh and PC versions of Excel. The following instructions created using the MAC version. Some PC differences are noted , but there might be more. Experimentation on your part might be necessary.
1.Hightlight the headings and data for the following columns: Age Classes, Number of Males, Number of Females. Click on the Chart Wizard button on the toolbar (the icon has a bar chart on it).
2.Select the following options when prompted by the Chart Wizard:
Bar (for chart type)
Choose the first sub-type (the one with the bars lying on top of each other). Click on NEXT.
The DATA RANGE window will open. As the directions on this page state, you must click in the data range box. Then on the
worksheet, select all the cells (Include text heading cells and data) that you want in your population pyramid. Typically this will be
three columns of information (Age class, Number of Males and Number of Females).
Behind the DATA RANGE window is a window called SERIES. Click on it.
On the SERIES PAGE, you need to REMOVE the age class data....(Why? I don't know, just do it!)
Next you must place the age class data you just removed into the category (X) axis labels: To do this. click on the box next to
Category X axis labels then immediately highlight on your worksheet the text label and data in the age class data column. (Yes this is the stuff you just removed!).
Click next....
This page of chart options allows you to add titles and text to your pyramid. EXPERIMENT! When done, click on next.
"As object in" your excel file (for the place to save your chart) You can place the chart wherever you wish. It is ok to place it either as a new sheet or as an object in the worksheet you are using.
Finish
3.To adjust various details of how the chart looks, you'll need to experiment a little. Here are a few that you need to change:
The labels for the vertical axis should be located at the "low" end (i.e. left side) of the chart. To fix this, double click on the vertical
axis line and choose *low* for the placement of your "tick labels."
The bars should be set to a "0" gap distance so they don't have space between them. To do this, double click on any one of the bars.
MAC: To change the gap distance, you must choose the following items:
* In the Format menu, choose "Chart Type"
* Choose "Options"
* Choose "Options" again
* Set the Gap didtance to "0"

 


A NOTE FROM A STUDENT IN A PREVIOUS CLASS:


When using Microsoft Excel to construct population pyramids, you can close the gaps by setting "Overlap" to 100 and "Gap
width" to 0. To select these choices, open the "Format Data Series" window. You can only access the "Format Data Series" window after the population pyramid is drawn. Once done, double-click on one of the bars. You can also access the "Format Data Series" window by selecting the "Format" drop-down menu from the toolbar at the top of the Excel page. On the menu, select "Selected Data Series..." to open the "Format Data Series" window. Once this window is open, select the "Options" tab, it should be the last one on the right. this will allow you to alter the gap width to 0 and the overlap to 100.


NOTE for both PC and MAC users: You should wait to change the gap distance until you are nearly done with your chart. Once
your bars are "touching" each other, it will be difficult to double-click on the vertical axis to make any changes to it. If you need to change the vertical axis AFTER you've changed the gap distance, you can change the gap distance BACK to 100, for example, and then change it back to zero later.


Adjust what you want to change and move on to the next feature that doesn't look the way you want it to look. KEEP IN MIND THAT THE PRINTED VERSION OF YOUR CHART MAY LOOK DIFFERENT THAN IT DOES ON THE COMPUTER SCREEN.


This chart can be copied and pasted directly INTO your cemetery report. Or you can print the chart out on its own page and include that page WITH your report.