The link between numerical experiments based on how
we believe processes should work and how the processes actually work lies
in how well the experiments can predict what we see. For example,
people developed theories for how elastic plates should bend when subject
to a load. Some have proposed that these theoretical constructs can
be applied to the Earth's crust. In order to determine if these theories
hold true for the upper crust, we might measure how the crust "bends" when
subject to a load and compare these observations to the constructs to see
if they hold true. If the theory well-predicts the observations,
then the theory is at least consistent with the observations and the observations
can not rule out the validity of the theoretical constructs. If the
theory poorly predicts what we see, then the theory must be revised (or
thrown out).
Spreadsheet programs give us a means of testing
our ideas about how something should behave against what we actually see.
In addition, if physically-based laws regarding processes we are investigating
are formulated, we can use Excel to both 1) find what values of the variables
of the physically-based equation(s) are the best values to match the data
and 2) how well these best fitting values actually predict the observations.
In this lab, we will use Excel to evaluate the validity of ideas about
how the crust should deflect under an applied load. We will first
build a simple numerical model of a bending piece of crust. Then
we will find the best fitting values for the variables for a given data
set. Finally, we will see how well the theory predicted the data
for the best fitting values.
Step 1- Build the numerical model.
The file "GLG490--Solver.xls" will be provided to you. In this file, data of deflections of the Earth's crust are given. The theory of elastic beams states that the deflection, v, at any point along the beam is a function of 1) position along the beam, 2) the applied load distribution, 3) the length of the beam, 4) the width of the beam, 5) the elastic properties of the beam. In other words,
v = f(x,q,L,w,E,nu)
where
x is the horizontal position along the beam.
q is the load distribution (may be a function of x)
L is the length of the beam
w is the thickness of the beam
E is the Young's Modulus of the material
nu is Poisson's Ratio.
For an elastic beam whose ends are fixed (no rotation or translation at the ends of the beam):
vcalc = (q/((E/(1/nu^2))*w^3)*((x^4/2) - ((x^2*l^2)/4) + (L^4/12))
in this problem, q = -1, and L=80.
Build an Excel spreadsheet which predicts the value
for v for every point x where there is a measured value.
Step 2- Calculate the RMS error
The RMS error is a guage of how well the model has predicted the data. The RMS error is computed as follows:
RMS = Square root(Sum((vobs-vcalc)^2))
The RMS error will be at its smallest value when
the values for the equation are the best-fitting values. For example,
if your RMS value is large, then the total difference between your vobs's
(observed data) and vcalc's (calculated data) is large and your
best fit is not good. However, if your RMS value is small, then the
total difference is small and your model well-predicts the data.
Step 3- Have Excel find the values for E, nu, and w which minimize the RMS error.
The Solver in Excel will be able to minimize a cell
by changing the values of other cells. First, open the Solver (under
the Tools menu). Next, tell Solver to minimize your RMS error cell.
Then, click down to the field that specifies which cells to change in order
to determine the minimum RMS error. These cells will be the cells
containing the values of your variables E, nu, and w. Highlight these
cells. Finally, click "Solve" and Solver will find the values for
the variables which give you the least error. These are your best
fitting values. The RMS error at this time will tell you how well
the model predicts the data: if the RMS error is low for the best fitting
variables, then the model is consistent with observed data. If the
RMS is high, the model does not predict the data very well, even using
the best fitting variables. If the RMS Error is high, we must consider
if the model needs revision.
Assignment:
For this lab, you are to produce two plots based on the flexure analysis:
1) Plot the observed and calculated deflection as a function of spatial position, x. Plot the calculated deflections as a solid line and the observed data as points. Make sure to put both of these on one graph.
2) Plot the square of the difference between the calculated and observed deflections as a function of x. This chart will show you where the model is predicting the observed data well (where the ordinate values are low) and where the model is not predicting the observed data well (ordinate values are high).
Mail the Excel spreadsheet with calculations and plots to glg490@asu.edu.