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 2009

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 Austin 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 these values are consistent with those determined directly from the USGS annual flows 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 operational on your computer: http://www.ce.utexas.edu/prof/maidment/StatWR2009/Ex1/Ex1HydroExcel.mht#_Toc221013181

This Distribution Spreadsheet has four 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.  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.  Use HydroExcel or other means to find another data series of interest to you and set up the Distribution Spreadsheet for these data.   Do they fit the normal or lognormal distributions?  Write 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?

You can get precipitation data from http://lwf.ncdc.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 pages of your spreadsheet.

3.  Calculate the mean, standard deviation and variance of a random variable X, whose distribution is uniform from 0 to 1.

4.  Use the Analysis Toolpak function Random Number Generation

to generate 1000 random numbers that are uniformly distributed from 0 to 1.  Use a seed of 1.  Calculate the cumulative mean of these 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 take the formulas you computed in (3) and predict the mean, standard deviation and variance you observe in these last 50 data values?