Geology 351  Geomath
Estimating the coefficients of various
Mathematical relationships in Geology
Throughout the semester you’ve encountered a variety of mathematical relationships between various
geologic variables such as age vs. depth, porosity vs. depth, earthquake magnitude and fault plane area, for
example. Very often our data are derived from observations and the parameters defining these various
relationships are useful to us as predictors in other situations. Geologists often deal with empirical data and
must form quantitative models of geologic behavior based on these observations. The objective of the current
lab is to provide you with experience in the use of spreadsheet functions to derive coefficients defining straight
lines, exponential functions, power laws and polynomials of the sort we have been studying.
Example 1:
GET INTO EXCEL
Let's start with a simple linear relationship such as the one suggested between age and sediment
thickness. Recall the data we worked with in an earlier lab that was taken from problem 2.11 in Waltham's text.
In this problem, data shown in the table below
*
were given for the Troll 3.1 well in the Norwegian North Sea.
This is data you used before and should have on your G:\Drive.
Depth
Age
19.75
1490
407
10510
545
11160
825
11730
1158
12410
1454
12585
2060
13445
2263
14685
* Data taken from Lehman, S., and Keigwin, L., (1992), Sudden changes in North Atlantic
circulation during the last deglaciation, Nature, 386, 757762.
You can place column titles Depth and Age in columns A and B, cells A1 and B1.
Generate a plot of the data
45
This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentYour plot should look like that
shown at right.
Now this plot clearly demonstrates
that the relationship between depth
and age is nonlinear. Earlier we
recognized that two straight lines
are needed to explain these data:
one curve for depths less than
500cm and another curve for
depths greater than 500 cm. The
graph shows us that sedimentation
rate decreased abruptly about
10500 years ago.
In our previous analysis of this data we estimated the rate
of change of age with change of depth from two points in
each area.
Depth
Age
Δ
Δ
was computed from data points with depths
extending from 19.75cm to 407cm and ages from
1490years
to 10510 years. This yielded
Depth
Age
Δ
Δ
=
cm
years
29
.
23
25
.
387
9020
=
.
We did a similar analysis of the deeper data and obtained
a more gradual age/depth relationship of
_________________(Remember how? Refer to your
earlier work).
L
a
s
t
1
0
y
e
r
0
500
1000
1500
2000
2500
Depth (cm)
0
2000
4000
6000
8000
10000
12000
14000
16000
Age (years)
Recent Sedimentation Record  North Sea
P
re
c
d
in
g
5
rs
What we want to do now is use Excel to determine the slope of these data for us, and we’ll limit our analysis to
the deeper data since the shallow data consisting of only two points is a trivial case – (there's only one possible
answer!).
Excel will compute a trendline through the data. The computation assumes that the deviations of Age from the
computed line are minimized. The line that Excel computes is the "best fit" line – trendline. But when Excel
computes a trendline, it considers all the data points, whereas in our earlier lab we considered only the two end
points and thus assumed that the other values fell along the general trend defined by those two points. This may
This is the end of the preview.
Sign up
to
access the rest of the document.
 Spring '09
 Linear Regression, Regression Analysis, Geology, Waltham

Click to edit the document details