CE 397 Statistics in Water Resources

Exercise 2

= ;

Frequency and Probability Distributions

= ;

By: David Maidment

Center for Research in Water Resources

University= of Texas at Austin

February 2= 009

I have prepared an Excel Distribution Spreadsheet template= for exploring the fitting of probability distributions using the normal and lognormal distributions for mean annual flows of the Colorado River at Aust= in from 1950 to 2008 as an example. These data are those derived in Exercise 1 of the course using HydroExcel’s capacity to make annual summaries of daily data, and I have verified that t= hese values are consistent with those determined directly from the USGS annual f= lows web site.

This spreadsheet is at: http://www.ce.utexas.edu/prof/maidment/StatWR2009/Ex2/ColoFlowDist.xls=

It uses the Excel Toolpak addin, so you’ll need to have this operat= ional on your computer: http://www.ce.utexas.edu/prof/maidment/StatWR2009/Ex1/E= x1HydroExcel.mht#_Toc221013181

This Distribution Spreadsheet has fo= ur tabs in it:

Data shows the time distribution of the data and their Descriptive Statistics and acts as an entry point for time series data from HydroExcel;

Histogram shows a frequency histogram of the data;

ChiSquare shows how to fit a normal or lognormal distribution to the data and check its fit using the Chi Square test;

ProbPlot shows how to do a normal or lognormal distribution plot of the data.

1.&= nbsp; Take the last 10 years of the Colorado River flows, 1999 to 2008 and compute by hand or with a hand-developed spreadsheet using the summation formulas (not the internal Excel functions) the mean, standard deviation, variance, median, coefficient of skewness and standard error of estimate of these data.   Verify that your results are consistent with those produced by the Descriptive Statistics tool in Excel.=

To be turned in: A printout of your results and computations.

2.&= nbsp; Use HydroExcel or other means to find an= other data series of interest to you and set up the Distribution Spreadsheet for = these data.   Do they fit the n= ormal or lognormal distributions? W= rite a little story about your data – where do they come from? What did you expect to see before = you analyzed them? What did you find out once your analysis was complete?<= /o:p>

You can get precipitation data from = http://lwf.nc= dc.noaa.gov/oa/climate/climatedata.html Instructions on how to use this web site are at: = http://www.ce.utexas.edu/prof/maidment/gradhydro2005/docs/ncdcdata.doc=

To be turned in: A printout or screen shot of all p= ages of your spreadsheet.

3.&= nbsp; Calculate the mean, standard deviation and variance of a random vari= able X, whose distribution is uniform from 0 to 1.

4.&= nbsp; Use the Analysis Toolpak function Random Number Generation

to generate 1000 random numbers that are uniformly distributed f= rom 0 to 1. Use a seed of 1. Calculate the cumulative mean of t= hese data.   Take the last 50 = summed values (ie from 950 to 1000) and fit a normal distribution to them. If you = sum uniformly distributed random variables, does the sum become normally distributed?   Can you ta= ke the formulas you computed in (3) and predict the mean, standard deviation and variance you observe in these last 50 data values?