Microsoft Excel for Statistics



PART 1: Statistics Using Microsoft Excel

(1) Frequency distribution.

Often we wish to compare the distribution of experimental data with the Gaussian distribution. We will use as an example the pebble data used in Statistics Practical 1.

(a) On a new Excel sheet enter the pebble data in A1:A51. Name this range as data.

7.6 8.1 8.2 8.5 8.8 8.9 9.0 9.1 9.2 9.2 9.3 9.3

9.3 9.3 9.4 9.4 9.4 9.4 9.4 9.5 9.5 9.6 9.7 9.7

9.8 9.9 10.0 10.0 10.1 10.2 10.4 10.5 10.7 10.7 10.9 10.9

11.1 11.3 11.3 11.5 11.6 11.8 12.2 12.6 12.7 12.9 13.4 13.5

14.2 15.7 17.3.

(b) Compute the mean and standard deviations of the data using the formulas =AVERAGE(data) and =STDEV(data) in C1 and C2 respectively and name them mean and stdev.

(c) Enter values 6,7,8,9,10 ….. 18 in B5:B17. These will be the bins values for the frequency formula.

(d) Enter an array formula as follows. With C5:C17 selected, enter =FREQUENCY(data,B5:B17) and use Shift+Ctrl+Enter to complete the formula.

(e) Compute the sum of frequencies with =SUM(C5:C17) in C18.

(f) To compute the expected frequencies based on a Gaussian distribution use these formulas.

D5: =$C$18*NORMDIST(B5,mean,stdev,TRUE)

D6: =$C$18*(NORMDIST(B6,mean,stdev,TRUE)- NORMDIST(B5,mean,stdev,TRUE))

Copy this down to D17, then modify the formula in D17 to be:

D17: =$C$18*(1-NORMDIST(B16,mean,stdev,TRUE))

(g) Calculate the sum of the expected frequencies with =SUM(D5:D17) in D18. It should be similar to the value in C18.

(h) Plot your results on a combined line and bar chart as shown below.

(2) Confidence levels and Student’s t-distribution

The following is based on question 4 of Statistics Practical 3. Use Excel to answer the following questions, compare the spreadsheet results with your calculated answers.

Volcanologists have measured the hydrogen content (in % of total number of atoms) of samples of gases collected from the 1970 and 1971 Mount Etna volcanic eruptions. Values are given in the following table:

|1970 |1971 |

|Hydrogen content (%) |Hydrogen content (%) |

|35.8 |38.5 |42.0 |45.0 |

|45.5 |36.0 |57.0 |44.6 |

|35.5 |40.5 |42.0 |48.5 |

|32.0 |35.5 |54.5 |63.0 |

|50.0 |45.5 |35.0 |55.0 |

|39.0 |37.0 |52.5 |40.0 |

|37.0 |36.0 |43.5 |37.5 |

|47.0 |53.0 |48.0 |53.7 |

a) Calculate a mean hydrogen value for the 1970 eruption and use Student’s t-distribution to find the 95% confidence limits for the true value.

You will need: average, sample standard deviation, number of measurements, standard error, confidence level, t value and confidence limits. Also insert the following equation into your spreadsheet:

(b) Use the Student's t-test for comparing means to determine whether there is a difference in the hydrogen content of the gases between the two eruptions at the 99% confidence level.

You will need: average, variance, number of measurements for each data-set, then t-calculated, confidence level, degrees of freedom, t-critical and result (null or alternative hypothesis). Insert the following equations into your spreadsheet:

(d) For arrays of equal size (as in our example where there are the same number of hydrogen measurements for both years) it is possible to use the TTEST function. Use this to function to calculate the probability that the two sets of measurements are different and then state your result.

For Q1&2 print-out your results and the graph from Excel. Also print-out the formulas by displaying them first by pressing Ctrl-` (` is next to 1 key on the keyboard).

Hand-in your answers as part of the assessment.

PART 2: Simple Monte Carlo model using Microsoft Excel

1. Geochronology

The following equation is used to calculate the age of a rock using the Ar-Ar dating method:

[pic]

t is the age of the rock in years

λ is a constant known as the decay constant and is 5.543 ( 10-10 years-1

R is the isotope ratio 40Ar/39Ar

J is the irradiation parameter

Given that R = 120.23 ( 0.74 and J = 0.015432 ( 0.000345, then calculate the age (in years) and use the Monte Carlo method to calculate the one standard deviation error on the age.

2. Locate the depth of a shallow earthquake

An earthquake sends out different kinds of waves that travel at different speeds. At a seismographic station, they thus arrive at different times. The difference in travel times is proportional to the distance.

[pic]

R is the distance from an earthquake to a station.

Vp is the speed of the P-wave.

Vs is the speed of the S-wave.

tp the time at which a P wave will arrive at the station

tS the time at which an S wave will arrive at the station

The speeds Vp and Vs vary for different depths and locations. Using: Vp = 6 km/sec and Vs = 3.43 km/sec.; tp = 2.53 sec; ts = 15.01 sec.

(a) Calculate R, the depth of the earthquake in kilometres.

(b) Derive and error propagation formula for calculating the error on R, assuming that only ts and tp have errors.

(c) Calculate the error on R assuming that tp and ts have one standard deviation errors of: (i) 0.5%; (ii) 1%; and (iii) 5%.

(d) Use the Monte Carlo method to calculate the approximate error on R for the same percentage errors used in (c). Do two repeat simulations for the 5% error using sample sizes of (i) 100 and (ii) 10,000 and comment on the results obtained.

Print out the results of your calculations and the formulae for both questions, and write comments for Question 2 on the relevant sheets. Hand in these as part of the assessment.

PART 3: Computer modelling using Microsoft Excel 1

Modelling the height of mega-tsunami waves

Background

Mega-tsunami are long wavelength (typically 300-400 km) wave trains that have sufficient energy to travel thousands of kilometres, across ocean basins at velocities far in excess of 500 km hr-1. In the open ocean the waves range from 10’s cm to metres high, depending on the energy of the triggering mechanism and distance travelled. However, 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. They wreak havoc on bordering coastlines, causing extreme erosion of bedrock (several metres) and rip up huge boulders (sometimes >1000 tonnes) from the sub-marine shelves and coastal platforms to 10's of metres above sea level. Mega-tsunami are far more destructive than waves generated by wind in severe storms or hurricanes due to their immense range, tremendous velocities (coastal velocities typically between 5-10 m s-1, reaching up to 200 m s-1 in focused jets) and enormous wavelengths that allow them to surge great distances inland.

The actions of small-scale tsunami, commonly triggered by large earthquakes, are frequently observed. Mega-tsunami, however, have never been witnessed historically although geological evidence for their existence is slowly being uncovered. Since they are typically erosional events, rather than depositional, they are easily overlooked, or misidentified, in the geological record. Despite this, several high energy deposits have been proposed as resulting from the action of mega-tsunami, e.g. at the Cretaceous-Tertiary boundary in Texas, in the Hawaiian Islands, Scotland and Norway, the Canary Islands, the Bahamas and New South Wales, Australia. Although tsunami is the most likely origin of these giant wave deposits (ranging up to 375 m above sea level), others argue that exceptionally large storm waves (much larger than any known historically) should be considered, particularly in the case of Hawaiian deposits.

Regardless of origin of these giant waves, they represent a major hazard. With ~1% of the world’s population (~60,000,000 people) living in regions susceptible to giant waves around the coastlines of the world’s oceans, they pose a very serious threat. The lack of historical examples of these giant waves necessitates that we look into the recent geological record for evidence.

Lateral collapse of volcanic islands – the detachment of huge coherent blocks (typically with dimensions of many 10’s of km) from the steep sub aerial and submarine flanks of volcanoes that may accelerate to great velocities (100's of m s-1) with energies of the order of 1017 to 1019 J – may also trigger giant tsunami. The Alika II collapse of Hawaii Island is thought to have triggered a giant tsunami that ran-up 375 m above sea level on neighbouring islands. Hawaiian lateral collapse has also been proposed as a potential trigger for tsunami action in New South Wales, Australia.

Description of model

Simplistic wave energy calculations can demonstrate that Hawaiian lateral collapses are capable of generating mega-tsunami in New South Wales.

The potential energy Ep (in Joules) released by lateral collapse is given by:

[pic]

V = volume of collapse block (m3)

ρw = density of seawater (1030 kg m3)

ρr = density of rock (2800 kg m3)

D0 = initial depth of sliding block (m)

D1 = final depth of sliding block (m)

g = acceleration due to gravity (9.8 m s-2)

Assumption 1: conversion of potential energy to tsunami energy is estimated to be of the order of 1% for submarine sediment flows and perhaps up to 20% for subaerial land slides.

For single long - period (tsunami) waves in water equal to or significantly deeper than wave height, the total wave energy Et:

[pic]

HD ( H, the wave height near shore

L = length of wave perpendicular to propagation direction (2πr, where r = distance wave has travelled).

Assumption 2: L is calculated for 360( wave; it likely to be directional in reality.

a) Calculate the potential energy (Ep) released from the ~200 km3 Alika II collapse falling from 2500 m to 4500 m below sea level.

b) Taking 1% as a conservative estimate for the conversion energy, calculate the total energy (Et) of the tsunami.

c) Assuming a crest length equivalent to a circular wave of radius 7000 km, then calculate the height for a single wave at the New South Wales coastline. Is the tsunami wave height adequate to account for the maximum 33 m run-up on the New South Wales coast?

d) For a multiple wave train, the energy would be shared resulting in smaller waves. For a train of n equal waves:

[pic]

Hence model the heights between a single tsunami wave and a train of up to 1000 waves for the Alika II collapse. Illustrate your results on a graph of wave height versus number of tsunami waves.

Print out the results of your calculations, your formulae and the graph and submit these for assessment.

-----------------------

[pic]

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download