EART20170 Computing, Data Analysis

1
EART20170 Computing, Data Analysis
Communication skills
Lecturer Dr Paul Connolly (F18 Sackville
Building) p.connolly_at_manchester.ac.uk
2. Computing (Excel statistics/modelling) 2
lectures assessed practical work Course notes
etc http//cloudbase.phy.umist.ac.uk/people/conno
lly LAST LECTURE!
Recommended reading Cheeney. (1983) Statistical
methods in Geology. George, Allen Unwin
2
Plan

• This lecture plus two more drop-in sessions in
  computer labs
• Assessment handed out today and need to hand in
  by 1600, Tuesday December 12th.

3
Lecture 5

• Monte Carlo method of error propagation.
• Using Goal seek to root-find
• Using solver for optimisation
• Basic macros.
• Mega Tsunami

4
Statistical approach to error propagation

• Computers enable the use of a very simple
  statistical method to propagate errors.
• Monte Carlo methods provides approximate
  solutions to a variety of mathematical problems
  by performing statistical sampling experiments.
• The statistical approach is particularly useful
  for propagating errors in complex functions.

5
Monte Carlo methods

• Monte Carlo simulations or methods are named
  after Monte Carlo, Monaco, where the primary
  attractions are casinos containing games of
  chance exhibiting random behaviour.
• The random behaviour in games of chance is
  similar to how Monte Carlo simulation selects
  variable values at random to simulate a model.
• For each uncertain variable (one that has a
  range of possible values), you define the
  possible values with a probability distribution
  (e.g. the Excel function norminv(rand(),mean,stdev
  )).

6
Monte Carlo in Error Propagation

• Lets use a previous example of measuring bed
  thickness. We have two populations of
  measurements x 12.1 0.3 and y 4.2 0.2.
• By repeatedly taking samples at random (e.g. by
  the nested Excel function norminv(rand(),mean,st
  dev)) from x and y, and adding the values, we
  should obtain a third population with a mean of
  16.3cm and a standard deviation approximately
  equal to that obtain from the analytical solution
  ( 0.4 cm).
• The statistical approach is particularly useful
  for propagating errors in complex functions

7
A more complicated formula
This is one used in geochronology (dont worry
about the details)
The error propagation formula is given by
where T 1/l
8
A more complicated formula
Using the following data R 49.704 0.381 t
1.072 0.011 billion years l 5.543 ? 10-10
years-1
Then using the equations J 0.016329
0.000255
OK so what about the Monte Carlo?
With a table of R and t calculated from the
norminv function (10000 values are typically used
for good statistics) we calculate J and can
therefore calculate the average and stdev.
9
Using Goal seek to root-find

• What if I want to find the inverse of a function?

?

• Sometimes I can find the inverse analytically,
  e.g.

• But not always (and if maths isnt your forte).

10
Using Goal seek to root-find

• What height of fall will result in a height of
  single tsunami 30 m?

• This is sometimes difficult (or impossible!).
• Instead use iteration gt Goal seek.

11
Using Goal seek to root-find

• Here are some arbitrary values

• Go to tools-gtgoal seek

12
Using Goal seek to root-find

• Enter the cell you want to change and the value
  (i.e. the actual energy) and the variable that
  will be changed press OK

• The cells change until the goal is found. Press
  OK at the next prompt

Your value for HD is now displayed in the correct
cell. And you didnt have to do any maths!
13
Using solver for optimisation

• Goal seek only works for functions of one
  variable.
• Goal seek is good for route finding, but what if
  I want to find other properties such as minimum,
  maximum values?
• E.g. Mining a gold seam. How can I break even?
  Whats the max profit I can make? Whats the min
  number of days I can mine before making a profit?

14
Using solver for optimisation

• Example say that it costs 100 per day to hire
  your basic digging equipment.
• And you manage to extract 4 tonne per day of gold
  from rock.
• But as the number of days increase it becomes
  more difficult to extract the gold from the shaft
  as extra equipment has to be rented usually
  have some a-priori knowledge (0.2xday2).
• The market value for gold is 321 for 31.1g.

15
Using solver for optimisation

• You wish to know
• How many days you should work before breaking
  even?
• What is the maximum net profit you can make
• How long can you work before your net rate of pay
  drops below 40 per day

16
Using solver for optimisation
17
First How many days to break even?

• Go to Tools-gtsolver

• On the pop-up menu, set the target cell to the
  Net cell reference and the changing cell to the
  Days cell reference. Also check the Value of
  tab and set this value to 0 (i.e. break even)

18
First How many days to break even?

• You should also set the constraint that the
  number of Days is greater than or equal to zero!
  Click on add and in the next box put in that
  Days should be greater than 0 OK.

• On the first popup window press solve

• The cell values change and another popup asks if
  you want to keep the solution OK.

19
First How many days to break even?

• You see that it after 243 days the venture will
  start to become non profitable. Your total costs
  were 36139 all of which you got back from the
  gold seam.

20
Second What is the max net profit?

• Go to Tools-gtsolver

• On the pop-up menu, set the target cell to the
  Net cell reference and the changing cell to the
  Days cell reference. Also check the max tab.

21
Second What is the max net profit?

• You should also set the constraint that the
  number of Days is greater than or equal to zero!
  Click on add and in the next box put in that
  Days should be greater than 0 OK.

• On the first popup window press solve

• The cell values change and another popup asks if
  you want to keep the solution OK.

22
Second What is the max net profit?

• You see that it takes 121.6 days to get the
  maximum net profit of 2960. Your total costs
  were 15113 and your average rate of pay was 24
  per day.

23
Third At least 40 per day?

• Go to Tools-gtsolver

• On the pop-up menu, set the target cell to the
  Rate cell reference and the changing cell to
  the Days cell reference. Also check the Value
  of tab and set this value to 40 (i.e. 40/day)

24
Third At least 40 per day?

• You should also set the constraint that the
  number of Days is greater than or equal to zero!
  Click on add and in the next box put in that
  Days should be greater than 0 OK.

• On the first popup window press solve

• The cell values change and another popup asks if
  you want to keep the solution OK.

25
Third At least 40 per day?

• You see that after 43 days your average net rate
  of pay will drop below 40. Your total costs were
  4687 and your net pay was 1730.

26
Basic macros

• The goal seek and solver tools are very powerful,
  but they can be time consuming if you want to
  work on vast data sets.
• You can save a macro to a worksheet and use it
  again and again without having to always remember
  the exact sequence.
• We will look at recording and using a macro for
  using the goal seek tool.

27
Basic macros

• Go to tools-gtMacro-gtrecord new macro

• You can name the macro and give it a shortcut key

28
Basic macros

• The macro recorder is now visible with a stop
  symbol. All your actions will now be recorded.

• Again use goal seek in the same way as before

29
Basic macros

• Enter your values as before.

• The solution is found

• Now press the stop button to cease recording

30
Basic macros

• You can now run your macro by going to
  tools-gtmacros-gtmacros

• Selecting the macro you recorded and pressing
  run. You could have also used a shortcut

31
A subtlety

• When using goal seek it is nearly always more
  convenient to solve for a zero.

• This is because goal seek doesnt allow the
  value to be input by a cell reference.

32
A subtlety

• In this case you put zero in the To value box
  in goal seek

Put zero here
33
Mega tsunami
34
Volcano collapse

• All volcanoes are inherently unstable and edifice
  growth will ultimately lead to some degree of
  collapse.
• Major collapse of the old volcanic edifice,
  Soufriere Hills volcano early on 26 December 1977

35
Caldera collapse

• The movement associated with collapse can be
  either vertical (caldera) or horizontal (lateral
  collapse).

36
Mount St Helens 1980
The landslide moved northward at speeds of 110 to
155 mph and advanced . Part of the avalanche
surged into and across Spirit Lake, but most of
it flowed westward along the North Fork of the
Toutle River for 13 miles filling the valley to
an average depth of 150 ft.
http//pubs.usgs.gov/publications/msh/debris.html
37
Hazard Potential

• Lateral collapse of oceanic island volcanoes are
  amongst the most spectacular natural events on
  Earth.
• There is a potential for submarine landslides to
  generate tsunami and mega-tsunami.
• Mega-tsunami have never been witnessed
  historically and geological evidence for their
  existence is controversial.
• With 1 of the worlds population (60,000,000
  people) living in regions susceptible to giant
  waves around the coastlines of the worlds
  oceans, they pose a very serious threat.

38
Mega-tsunami

• Mega-tsunami are long wavelength (typically
  300-400 km) wave trains that travel thousands of
  kilometres, across ocean basins at velocities in
  excess of 500 km hr-1.
• As they pass into shallower water towards land
  their wavelength is compressed and height
  amplifies, typically 10- to 20-fold, generating
  waves up to hundreds of metres high that may
  incur many kilometres inland.

39
Hawaiian lateral collapses

• The Hawaiian islands are surrounded by more than
  68 slumps and avalanches gt20 km long.
• There are gt20 giant collapses of up 5000 km3
  (approx. 2000 times larger than Mt St Helens)

From http/www.mala.bc.ca/earles/kilauea-feb02.h
tm
40
From Ward, 2002
41
Prehistoric Hawaiian Collapse
USGS http//vulcan.wr.usgs.gov/Volcanoes/Hawaii/Ma
ps/map_location_hawaii.html
42
Lanai tsunami impact
HAWAII
Source of
PACIFIC OCEAN
tsunami
NEW GUINEA
Wave
impact
FIJI
AUSTRALIA
Wave
Sydney
impact
Wave
impact
NEW ZEALAND
TASMANIA
From Davidson, 1992
43
New South Wales Tsunami Deposits
The tsunami carved these scour pools within a few
minutes as it overtopped a 20-25 m high headland
Blocks stacked against against 30 m high cliffs.
Note the person circled for scale. Some of the
blocks are as large as rooms in a house.
Source E.A. Bryant http//www.uow.edu.au/science/
geosciences/research/tsun.htm
44
Tsunami wave model
Potential energy released by the collapse
Archimedes force
D0
Ds
V volume of collapse block (m3) pw density of
seawater (1030 kg m3) pr density of rock (2800
kg m3) g acceleration due to gravity (9.8
m/s/s) D0 initial depth of sliding block (m) Ds
final depth of sliding block (m)
45
The wave energy, Et
HD H, the wave height near shore (Depth 0)
L length of wave perpendicular to the
propagation direction