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

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.

Lecture 5

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

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.

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

)).

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

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

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.

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

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.

Using Goal seek to root-find

- Here are some arbitrary values

- Go to tools-gtgoal seek

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!

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?

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.

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

Using solver for optimisation

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)

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.

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.

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.

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.

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.

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)

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.

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.

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.

Basic macros

- Go to tools-gtMacro-gtrecord new macro

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

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

Basic macros

- Enter your values as before.

- The solution is found

- Now press the stop button to cease recording

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

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.

A subtlety

- In this case you put zero in the To value box

in goal seek

Put zero here

Mega tsunami

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

Caldera collapse

- The movement associated with collapse can be

either vertical (caldera) or horizontal (lateral

collapse).

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

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.

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.

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

From Ward, 2002

Prehistoric Hawaiian Collapse

USGS http//vulcan.wr.usgs.gov/Volcanoes/Hawaii/Ma

ps/map_location_hawaii.html

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

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

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)

The wave energy, Et

HD H, the wave height near shore (Depth 0)

L length of wave perpendicular to the

propagation direction