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):

            v_calc = (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((v_obs-v_calc)^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 v_obs's (observed data) and v_calc'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.