Assignment #3 - Calculating and Graphing with Excel

In this exercise you will learn how to manipulate a spreadsheet for calculations and to produce charts.

Pressure and Depth Within the Earth

Calculate the pressure within the Earth as a function of depth to 600 km. To do this, assume

• An 25 km upper crust of granite, density 2750 Kg/m^3
• A 10 km thick lower crust of basalt, density 3000 Kg/m^3
• An upper mantle of density 3300 Kg/m^3 to a depth of 400 km
• A transition layer of density 3600 Kg/m^3 between 400 and 670 km

You will need to integrate the equation for hydrostatic pressure P as a function of depth z, density rho and gravity g:

dP/dz = rho*g __________(1)

Assuming the pressure P is zero at the surface (z=0), we can write

P = Int_0^z{rho*g*dz} ___________(2)

You will need to make assumptions about the depth dependency of g.

Q1: Looking at Table 1, what can you assume about g?

Replace equation (2) by a finite difference equation in which the pressure p(i) at the base of layer (i) is related to the pressure at the base of the previous layer (i-1).

Q2: Write down this finite diference equation

2. Now go to Excel and open a new sheet; save it as assign5.xls

• Make 3 columns with headings for z, rho and P
• Put the appropriate units under the headings
• Enter depths 0 to 50 km in increments of 5 km
• Enter depths of 100 to 600 km in increments of 50 km
• Enter the appropriate densities from the problem (above)
• Translate your finite difference formula into a form suitable for spreadsheet calculations.
  [Hint: construct the appropriate formula for the second depth, 5 km; when you are satisfied it is correct, copy this formula to the rest of the rows. Be careful about units; they should be km for depth, kg/m^3 for density and Gpa for pressure].

Q3: Write down the spreadsheet formula used in the column for pressure.

3. Enter the values for the pressure from Table 1 as a 4th column in your spreadsheet - these represent a more accurate seismological Earth model.

Q4: Between the surface and 600 km, what is the mean % difference between your rough integration for pressure and the values in Table 1?
[Hint: use the spreadsheet functions to get this].

4. Use the charting functions of Excel to make a plot of the density rho and your computed pressure P (a) between 0 and 600 km and (b) between 0 and 50 km.

• Put pressure on the left axis and density on the right (secondary) axis.
• Use a blue line, and blue on white circular symbols, for the density and a red line with red on white square symbols for pressure.
• On the legend, identify the two curves.
• Make a title "Density and Pressure in the Earth" and label the x-axis "Depth (km)", the left axis title is "Pressure (GPa)" and the right axis is "Density (Kg/m^3)"

Q5: Comment on the effect of the density changes on the pressure in plot 4(a) - how do the values differ from a straight line?

Send the two plots to the color printer. Hand in the plots and, on a separate sheet, the answers to the questions.

----- end of Assignment #3 ---------