Geology 101 Lab: Geologic Time (Using Excel)

Answer sheet (click here).

What you'll learn:

What you'll turn in:

  1. Excel spreadsheets that contain your data and charts.
  2. Completely labeled XY (Scatter) graphs, printed out with white (clear) backgrounds, produced in Microsoft Excel.
  3. Answers to all written questions.

Part 1: Absolute Ages from Radioactive Parent Isotopes and Daughter Products

A. Using Excel, create a graph of radioactive decay over the course of 10 half-lives.

Open up a new Excel spreadsheet (workbook).

Make a graph of radioactive decay over the course of 10 half-lives, as follows. You get to choose the wording of the labels.

  1. Label the first column, which will be the number of half-lives that have elapsed. Enter the numbers 0 through 10.
  2. Label the second column, which will be the fraction (out of 1) of parent isotope remaining. In next cell down of this column, in the cell to the right of 0 elapsed half-lives, enter the number 1.
  3. Label the third column, which will be the fraction of daughter product isotope that has accumulated as a result of radioactive decay of the parent isotope.
  4. Make your graph:

Questions about radioactive decay. (Note: Answer all written questions on the separate answer sheet.):

  1. What is a half life?
  2. After one half-life has elapsed, what fraction of the parent isotope will remain?
  3. After two half-lives have elapsed, what fraction of the parent isotope will remain?
  4. After three half-lives have elapsed, what fraction of the parent isotope will remain?
  5. To the nearest 0.01, after 0.5 of a half-life has elapsed, what fraction of the parent isotope will remain? (If you think the answer is 0.75, look closer.)
  6. To the nearest 0.01, after 1.3 half-lives, what fraction of the initial amount of a parent isotope will remain?
  7. To the nearest 0.01, when 0.3 of the parent isotope remains, how many half-lives will have elapsed?


B. Use Isotope Ratios and a Mathematical Formula to Measure Radioisotope Ages.

The following table lists some of the main isotope systems used for radiometric dating.

Table 1
Half Life(years)
Dating Range(years)
Uranium-238 Lead-206 4,500 million 10 - 4,600 million Minerals include zircon, uraninite. Igneous or metamorphic rocks.
Potassium-40 Argon-40 1,300 million 0.05 to 4,600 million Minerals include muscovite, biotite, K-feldspar. Volcanic rocks.
Rubidium-87 Strontium-87 47,000million 10 - 4,600 million Minerals include muscovite, biotite, K-feldspar. Igneous or metamorphic rocks.
Carbon-14 Nitrogen-14 5,730 years 100- 70,000 years Not used for dating rocks, except carbonates from Earth's surface such as recent coral reefs. Used for young organic materials, or surface-water samples: Wood, charcoal, peat, bone, tissue, carbonate minerals from surficial environments, water containing dissolved carbon.

From the last three questions in part A, you may get the sense that there are limits to the eyeball method of reading a graph to get the number of half-lives from the fraction of isotopes.

There is a clear mathematical relationship between the amount of a radioactive isotope (and the corresponding amount of daughter product), and the number of half-lives that have elapsed. The relationship is non-linear, involving some sort of logarithm or exponent.

The mathematical formula can be used to determine exactly how many half-lives have elapsed, for any fraction of parent/daughter isotope, without having to make a visual estimate from a graph. In this next (and last) spreadsheet exercise, you will use the mathematical formula.

Start with the data below, from six different geological samples.

Table 2
Sample #
Isotope system: Parent---Daughter
Daughter Isotope-Parent Isotope (D/P) ratio
  1. Open up a new Excel spreadsheet (workbook).
  2. Enter the data from the preceding table, samples 1-6. You can cut and paste the data directly online, or else type it in.
  3. Now calculate the six ages.
  4. Given a half-life of an isotope and the daughter/parent isotope ratio (D/P), the age equals the half-life times the natural log of [(D/P)+1], the preceding result then divided by the natural log of 2.

    Age = (Half-life) X ln[(D/P) + 1]/ln2

    (where the half-life is for the particular isotope, D/P is the
    daughter/parent isotope ratio, and ln means natural log)
  5. Enter this formula in Excel, to the right of the cell that has the number 1.51:

    =(half life number)*LN([click on cell that has the D/P ratio of 1.51]+1)*(1/LN(2)).

    If your data is set up in the same cells as this example, your actual formula may read as follows:


    where 5,730 is the half life of the isotope in this sample.
  6. This should return a result of 7,608 years. At the top of this column, label it. For example: "Age (yrs)."
  7. Copy the formula down the same column five more times.
  8. You should now have all 6 ages calculated.
  9. For samples 3 and 4, change the half life in the formula to the half-life of K-40 (see Table 1, above).
  10. For samples 5 and 6, change the half-life in the formula to the half-life of U-238 (see Table 1, above).
  11. Round each age off to three significant digits. The first age will round from 7,608 years to 7,610.
  12. You may choose to type the rounded ages into the next column to the right, with a label such as "Rounded ages (yrs)."

Now answer the following questions on your lab answer sheet.

  1. Why can't carbon-14 be used to measure the ages of rocks? Give two reasons.
  2. Why can't carbon-14 be used to measure the ages of dinosaur bones?
  3. What must have happened to an isotope system in a rock, since the rock formed, for a radioisotope age to be valid?






C. Using Relative Dating to Deduce the Geologic History of the Grand Canyon.

The following figure, "Generalized Rock Column of the Grand Canyon," comes from a University of New Mexico geologic history project done in conjunction with with Grand Canyon National Park, the "Trail of Time" exhibit, at the Web site

Note: There is further information, and another stratigraphic view, of the Grand Canyon on the Kaibab Natural History Association's Web page on Grand Canyon rock layers at: Kaibab Natural History (


Get some colored pencils.

On a printout of the figure below (see if your instructor provides you with one), mark a major nonconformity with green lines, a major angular unconformity with red X's along the unconformity, and a major disconformity with a red line.


The following two paragraphs and geologic column (Table 3:) comes from a geology 101 online assignment at the University of North Dakota, at

Below is a geologic column for the Grand Canyon.Each formation is more or less described. This is the type information that geologists use to construct geological histories.

Note that squiggly lines in the column shown above and dashed lines below indicate unconformities. As you know, that means that something is missing, possibly because the land was high and being eroded at that time! I have only marked the most significant unconformities. They occur above and below the Zoroaster Granite, at the base of the Temple Butte Limestone, and at the base of the Surprise Canyon Formation.

Table 3

Permian Kaibab Limestone Gray, sandy, massive, limestone up to 320' thick. Abundant fossils include corals, squids, sponges, and shellfish.
Toroweap Formation Red and Yellow sandstones at the top and bottom of the formation, and some limestone between. Common fossils include corals, sponges, sharks teeth, and many kinds of clams, etc.
Coconino Sandstone A massive white to buff colored, cross-bedded sandstone about 400' thick. Almost all quartz, well sorted, fine grained, and displays huge aeolian cross-bedding. Trails of quadrupeds, either reptiles or amphibians, have been found.
Hermit Shale 100-300' thick, predominantly shale but also includes some sandstone strata. The sandstones have a deep red color. Some shale shows mud cracks and ripple marks. Fossils of plants, mostly ferns, and quadruped footprints have been found.

Supai Group:

  Esplanade Sandstone
  Wescogama Fm.
  Manakacha Fm.
  Watahomig Fm.

1000' thick series (group) of alternating red cross-bedded sandstones and shales. The upper part of the group is non-marine and tracks of quadrupeds are found on bed tops. These tracks are believed to have been made by amphibians or primitive reptiles. Sediments appear in many places to be thin beds spread over wide areas in short periods of time. The lower part of the Supai includes calcareous sandstones and shales which many believe are of marine origin.

Surprise Canyon Formation

-- unconformity --

Variable deposits include sands and conglomerates. Cross bedding is common but localized. Great horizontal variation in stratigraphy.
Redwall Limestone Thick to massively bedded, bluish-gray limestone beds up to 600' high. The most conspicuous cliff in the canyon. It appears red, but that is only on the surface. Various invertebrates, including corals, shellfish, and crinoids are present as fossils.
Devonian Temple Butte Limestone

-- unconformity --
A calcareous sandstone, lavender to purplish colored, 50-100' thick. Fossils of armored fish, corals, shellfish and snails have been found.

Tonto Group:

Muav Limestone
Bright Angel Shale
Tapeats Sandstone

The Muav Fm. is a gray to buff limestone 300-400' thick. At its base, the limestone is interbedded with green shale and sandstone. At its top the limestone grades into brown shales and sandstone.
The Bright Angel Shale is mostly thinly bedded sandstones, but conspicuous micaceous shales and dolomite beds are also present. It varies between 350 and 400' thick.
The Tapeats Sandstone is massive, coarse to medium grained, 100-300' thick. It is generally chocolate brown, but is lighter colored in some areas. Cross beds are very common. Ripple marks, showing strong currents in one direction can also be found. Trilobite (an extinct early crab like critter) trails and worm tracks are present.
Late and Middle Precambrian Dox Sandstone Sandstone mixed with limey shale. Ripple marks and cross bedding are present. Up to 1700' thick but variable.
Shinumo Quartzite Thick bedded, massive, white, variable color, 1100' thick in some places. Many cross beds and ripple marks on a fine scale. May form cliffs locally.
Hakatai Shale 800' thick reddish and vermilion mudstones and shales interbedded with minor sandstone. Ripple marks, mud cracks, raindrop impressions are common. This formation is generally eroded to a smooth slope.

Bass Limestone

on top of
-- unconformity --

Mostly gray dolostones, weathered dark brown in places. Up to 200' thick. Interbedded shales and sandstones are present, often showing ripple marks. Fossil algae have been found.
Early Precambrian Zoroaster Granite
Although stratigraphically below the Vishnu Schist in many parts of the canyon, this intrusive igneous rocks is actually younger in age. Granites of this sort are associated with uplift and formation of mountain ranges.
Vishnu Schist Oldest rock in the canyon. Formed by metamorphism of rocks that were originally sedimentary. The metamorphism occurred after the rocks were buried to great depth by mountain building. This formation is now tilted up and in places approaches vertical. Thickness unknown.





















Your last assignment, the final part of the lab, is to write a geologic history of the Grand Canyon's rocks, based entirely on the Generalized Rock Column (above), Table 3 (above), and the Kaibab Natural History Association Web page, To perform the assignment:

Return To Top