Geology 351 - Geomath
Computer Lab – Introductory Problem (using Excel)
Evaluating Depth/Age Relationships in the North Sea
The following instructions take you step-by-step through the generation and plotting of a data set using Excel. Many of you may already be familiar with Excel, but if you aren’t that’s not a problem. In future exercises, you will learn how to fit straight lines and polynomials of higher order to specific data sets. Today, we are going to plot up some data and take a conceptual look at some linear relationships we will see within the context of age-depth relationships for sediments deposited in the North Sea. We will learn how to generate xy data plots and format them in various ways.
GETTING INTO EXCEL
Follow along as we bring up Excel. Access to various controls are arranged around the “ribbon” across the top of the Excel window. There are several folders across the top that one accesses to perform various tasks. To open a file, go to the office button in the upper left corner and click on it. In the drop down list you will see an open file option. Left click on FILE then browse to your data folder. In most cases this might be on the Desktop under your favorites list (upper left). A lot of the class files will reside on a network folder. Before getting into Excel go to the “common drive” and copy the FittingLabData folder over to your N:\Drive. The N: Drive is another network drive that is linked to your account and can be accessed from any computer in the building that is on the network. This is your personal file storage area. We’ll spend some time in class reviewing all this (take notes!). For today’s lab, select the data set DepthAge.xls and click the open button. A spreadsheet or window containing the depth and age data will appear on the screen.
The data in the spreadsheet were taken from Chapter 2 of Waltham's text (page 37, problem 2.11). The following data shown in the display below were taken from the Troll 3.1 well in the Norwegian North Sea.
Data taken from Lehman, S., and Keigwin, L., (1992), Sudden changes in North Atlantic circulation during the last deglaciation, Nature, 356, 757-762.
The first row contains the column labels Depth and Age (in cells A1 and B1) defining the data type listed in each column.
GRAPHING AND PLOTTING!
Let’s see what this data looks like.
Click the insert folder tab on the Ribbon (note variety of options)
Click on the Scatter plot icon in your Quick Access toolbar. You’ll see a variety of plot styles in the drop-down window. Select plot style with data points and curved line fit.
After you click on the Scatter plot option a blank plot window comes, select columns A and then B to specify the x and y plot data (follow along in class and take marginal notes).
Now that you’ve made this nice plot delete it!
NOTES
Another way – First select the data and then select the scatter plot option again. The plot should automatically come up (see next page). Let’s put this plot option in your Quick Access Toolbar. Right click on the scatter plot option and select Add to Quick Access Toolbar.
NOTES! Notes!
Plot generated by selecting the columns first. This plot also has formatted axis labels and chart layout.
ODDS and ENDS -
Axis Labels
Click on axis labels and correct or change as needed. Add units in parentheses: eg Depth (cm) & Age (years).
General Formatting
From Format> Quick layouts> select the style of plot you’d like
Also right click on the plot line and select Format Data Series.
Review:
How do you bring in your data?
How do you plot your data?
How can you change the plot line style (color, thickness, continuous or dashed…)?
How can you change the data point display style (marker line style, line color, …)?
How do you get back to the Chart Tools?
How do you change the chart layout?
How do you format gridlines, chart axes?
How can add a chart title?
How do you move the graph around in the chart window?
What are the three tabs available under Chart Tools
Do you have any questions?
Printing your plot
Send your plot to the printer, but first change the title of your plot to include your name.
If you don’t have a title, go to go to the Add Chart Elements icon at left. Check out the More Title Options. Or, if you already have a title on your chart, right click and select Format Chart Title.
You can change axis labels the same way.
You can also add a text box. A good item to put in your Quick Access toolbar. The text box along with the elements in your Shapes drop down will allow you to label, add arrows and point out features of interest.
Try this out now.
Printouts go to the printer room between the labs
NOTES:
Click on your text box and arrows; move them around. Change their formats.
Back to Problem 2.11: From the graph and data listing, estimate the sedimentation rate for the last 10000 years, (ii) the sedimentation rate for the preceding 5000 years, and (iii) the time since sedimentation ceased.
i) Compute the sedimentation rate from data points using depths extending from 19.75cm to 407 cm and ages of 1490 years to 10510 years. This yields: .
ii) This question can be answered in a couple ways: we could fit a regression line to the data with ages of 10500 years and greater; or we could make a quick estimate by using the end points on the line and assume the age varies linearly along a line between those two points. So we just compute the rate the same way we did in part i) but using the end points shown above.
III) To answer this question we have to find the slope of the line considered in part i. Using the equation of a line (Age = Slope x Depth + Intercept) you then solve for the intercept. Remember you know that the depth equals zero at the surface so the intercept is the age of the sediments at the surface. What else have we assumed in making this estimate?
GENERATING A LOG PLOT-
Remember from our in-class discussions of possible age depth relationships we suggested increased depth of burial would compact sediments and reduce their porosity. Thus the age-depth relationship could be variable through time. Increased depth of burial would increase the amount of time represented by a unit thickness. For example, a meter thick section of strata buried at 5000 meters might have taken 40,000 years to deposit, whereas a meter thick section in the near-surface might span only 10,000 years or so. Could the age of these North Sea sediments increase exponentially with depth in the form?
Do you remember how to take the natural logarithm of both sides of this equation? What kind of equation results from this operation?
Let's check it out.
Go to Chart Tools > Layout > Axis > Primary Vertical Axis > More Primary Axis Options and check the logarithmic scale check box. You also have the option to change the min and max values along with other parameters. Note in the figure below that the Age axis and gridlines have been rescaled to equally proportioned logarithmic intervals (base 10).
Does your plot look more like a straight line?
Here’s some data where age does increase exponentially with depth.
Now that you’ve gone through the basics of constructing a graph or chart and printing it off, take some time to go back over what you’ve done and play around with the different formatting options. The best way to learn will be to experiment.
MORE NOTES?
Save your data. The best place to save will be on your N:drive.
Go to your Office Button (Upper left)
Click on the Save or Save as icon and use the N:drive
Give your file a name you will remember like problem 2-11.
Files saved on the g:drive can be accessed from other computers in this lab or other Brooks Hall computers tied into the department computer network. If you save to the c:drive on the machine you are currently using you will only be able to access your data from that machine.
Problem 2.12(computing liquid phase concentrations)
As crystals settle out of magmas, the element concentrations (C in formula below) in the remaining liquid fraction change according to the equation
where C0 is the initial concentration of the element in the liquid before crystallization began, F is the fraction of liquid remaining and D is a constant (known as the distribution coefficient). Calculate the concentration of an element after 50% crystallization (i.e. F = 0.5) if its initial concentration was 200ppm and D=6.5.
Let's take a different approach to the solution of Problem 2.12. Rather than solving C for just one value of F let's solve C for a range of Fs extending from 0 to 1 at intervals of 0.05. This way, we get a comprehensive look at the function and how it varies.
This will give us a total of 21 computations of C. Sounds like a lot of work, but we’ll use some autofill operations and can probably do all this in the time it would take you to do one computation by hand.
First -
Move into the second spreadsheet in your Excel Workbook. While we’re at it, let’s give each spreadsheet a name. Call Spreadsheet 1 Prob2-11 and Spreadsheet 2 Prob2-12. Just double click on the sheet tabs and you’ll find that you can type names and format these Sheet tab labels.
Next identify your variables in row 1 cells A1 and B1. Type F into cell A1 to indicate that column 1 will contain values of remaining liquid fraction. Type C into cell B1 to indicate that column 2 contains the calculated element concentrations. Since F is our independent variable, we need to enter values of F over the range from 0 to 1 in increments of 0.05. To do this type in the first two values: 0 in Cell A2 and 0.05 in Cell A3.
Then left click on Cell A2 and drag down to Cell A3 and lift up on the left mouse button. Your screen should look like that shown at left.
To fill out the remaining cells from 0.1 to 1 just right click on the lower right corner of the highlighted cells and drag down the column. Note that as you drag down the column, there will be a little post box next to the current location of the mouse arrow telling you what the value of F is in that cell so you know when to stop! Your sheet should look like that shown below.
Now that you have your independent variables specified over the range 0 to 1, we are ready to calculate the values of . Before we do this though add a third column title CO in cell C1 and then enter the value 200 in cell C2 (see figure above). Then highlight cell C2 and assign a variable name CO to this cell in the cell identification box above Cells A1 and A2 (see circled entry below).
Now we are ready to calculate element concentrations C. In cell B2 enter the formula
=CO*A2^(D-1)
* represents the multiplication operator
^ represents the exponentiation or power operator
CO is the initial concentration of 200 ppm
D is the distribution coefficient with value 6.5
The equation says
.
Now, highlight this single calculation cell (Cell B2) and then place your mouse on the little black handle on the lower right of the cell box. When you get the plus sign, left click and drag down to cell 22. Note all values of C have been automatically calculated (see below).
How does concentration (C) vary with liquid fraction (F)?
Remember how to generate a plot? Select columns A and B;. Click on the Ribbon Insert tab; Select the scatter plot; under the Chart Layout options drop down list in the design tab, select the gridded style plot (lower left). Under the Layout Tab specify labels, titles, etc. Put those professional touches on your plot display.
In the above setup we specifically assigned values to the variable Co and D. While you have your plot up change these values and note that the values of C are automatically recalculated and your plot is updated.
Bring questions to next class. We’ve covered a lot of ground today so make sure you spend some additional time outside of class working over the procedures we’ve covered.
Due date for Problems 2.11 and 2.12 to be decided-
Presentation Outline – Computer Problem Set 1
Problems 2-11 & 2-12
2-11
a) Present the graph of Age versus Depth (5 points).
Label to note regions with different sedimentation rates.
b) Present your calculations. Organize them in a step by step fashion. Don’t just write down the answer. Show the details.
i) What was the sedimentation rate during the past 10,000 years? (2 points)
ii) What was the sedimentation rate during the preceding 5000 years (~ 10,000 to 15,000 years ago)? (4 points)
iii) When did sedimentation cease? (4 points)
2-12
a) Present your graph. (5 points)
b) Present hand calculations of the concentration after 50% crystallization. (3 points)
c) Using your calculated data tables compare the change in concentration from 85% to 75% liquid fraction with that occurring between 55% and 45%. (2 points).
20