Frequency analysis of Elkhorn historic discharge data

Below is a portion of an Excel table used to analyze the history of discharge and the frequency of different size flood events in the Waterloo area of the Elkhorn. The following steps were used to create this chart.

• Information was copied from a query to the USGS database via the web and pasted into Excel. 81 years of data were available.
• The Text to Columns option was used to parcel the text into space delineated columns.
• All the data except year and date and value of peak discharge was deleted, resulting in the first two columns below.
• The data was sorted in descending order, resulting in the next two columns.
• A rank value was assigned to each year with 1 as the largest.
• The recurrence interval (R.I.) was computed using the formula R.I. = n+1/rank.
• A probability value that a flood this size or larger would occur in any given year was calculated and is equal to 1/R.I.
• The log of R.I. was taken for the last column.

 year discharge year sort of dis rank R.I. year p value log RI 1899.06.13 3700 1944.06.12 100000 1 82 0.01219512 1.91381385 1900.09.13 4160 1962.03.29 50200 2 41 0.02439024 1.61278386 1901.06.23 4740 1960.04.02 46900 3 27.3333333 0.03658537 1.4366926 1902.07.10 7780 1984.06.18 43100 4 20.5 0.04878049 1.31175386 1903.06.03 8680 1971.02.22 41700 5 16.4 0.06097561 1.21484385 1911.06.30 3670 1967.06.16 39600 6 13.6666667 0.07317073 1.1356626 1912.04.02 30000 1978.03.21 38900 7 11.7142857 0.08536585 1.06871581 1913.05.18 5740 1990.06.19 37000 8 10.25 0.09756098 1.01072387 1914.06.16 5750 1996.08.07 35100 9 9.11111111 0.1097561 0.95957134 1915.07.17 19600 1993.03.11 33500 10 8.2 0.12195122 0.91381385 1929.06.19 6620 1966.02.11 30100 11 7.45454545 0.13414634 0.87242117 1930.05.12 12600 1912.04.02 30000 12 6.83333333 0.14634146 0.83463261 1930.11.21 2120 1951.03.27 27500 13 6.30769231 0.15853659 0.7998705 1932.05.07 10900 1999.08.08 27300 14 5.85714286 0.17073171 0.76768582 1933.07.13 7020 1998.09.06 26300 15 5.46666667 0.18292683 0.73772259 1934.06.09 8040 1991.06.06 24300 16 5.125 0.19512195 0.70969387 1935.06.06 3300 1987.03.25 23500 17 4.82352941 0.20731707 0.68336493 1936.03.08 16000 1995.05.29 23400 18 4.55555556 0.2195122 0.65854135 1937.06.20 2920 1940.06.07 22900 19 4.31578947 0.23170732 0.63506025 1938.07.08 7450 1949.03.07 21000 20 4.1 0.24390244 0.61278386 1939.03.13 7500 1986.03.19 20500 21 3.9047619 0.25609756 0.59159456 1940.06.07 22900 1958.08.01 20300 22 3.72727273 0.26829268 0.57139117 1941.03.10 7080 1997.06.21 20200 23 3.56521739 0.2804878 0.55208602 1942.06.20 6500 1982.05.20 19800 24 3.41666667 0.29268293 0.53360261 1943.06.14 8570 1915.07.17 19600 25 3.28 0.30487805 0.51587384 1944.06.12 100000 1965.09.30 19200 26 3.15384615 0.31707317 0.4988405 1945.07.17 13600 1974.05.19 19200 27 3.03703704 0.32926829 0.48245009 1946.02.07 5720 1992.06.17 19100 28 2.92857143 0.34146341 0.46665582 1947.06.13 14100 1964.06.17 18800 29 2.82758621 0.35365854 0.45141585 1948.03.19 14300 1983.06.19 18700 30 2.73333333 0.36585366 0.4366926 1949.03.07 21000 1969.06.27 18500 31 2.64516129 0.37804878 0.42245216 1950.03.06 16700 1985.04.27 18500 32 2.5625 0.3902439 0.40866387 1951.03.27 27500 1950.03.06 16700 33 2.48484848 0.40243902 0.39529991 1952.03.13 14200 1936.03.08 16000 34 2.41176471 0.41463415 0.38233494 1953.06.09 14300 1994.03.06 15800 35 2.34285714 0.42682927 0.36974581 1954.06.22 15400 1954.06.22 15400 36 2.27777778 0.43902439 0.35751135 1955.07.14 6440 1979.03.18 14800 37 2.21621622 0.45121951 0.34561213 1956.06.26 6960 1948.03.19 14300 38 2.15789474 0.46341463 0.33403026 1957.06.17 13000 1953.06.09 14300 39 2.1025641 0.47560976 0.32274925 1958.08.01 20300 1952.03.13 14200 40 2.05 0.48780488 0.31175386 1959.05.31 13400 1947.06.13 14100 41 2 0.5 0.30103 1960.04.02 46900 1945.07.17 13600 42 1.95238095 0.51219512 0.29056456 1961.06.02 9480 1959.05.31 13400 43 1.90697674 0.52439024 0.2803454 1962.03.29 50200 1957.06.17 13000 44 1.86363636 0.53658537 0.27036118 1963.06.25 7350 1930.05.12 12600 45 1.82222222 0.54878049 0.26060134 1964.06.17 18800 1980.05.28 12200 46 1.7826087 0.56097561 0.25105602 1965.09.30 19200 1932.05.07 10900 47 1.74468085 0.57317073 0.24171599 1966.02.11 30100 1975.06.22 10800 48 1.70833333 0.58536585 0.23257262 1967.06.16 39600 1989.09.05 9950 49 1.67346939 0.59756098 0.22361777 1968.06.26 6540 1961.06.02 9480 50 1.64 0.6097561 0.21484385 1969.06.27 18500 1977.05.28 9200 51 1.60784314 0.62195122 0.20624368 1970.04.22 3310 1972.07.27 9160 52 1.57692308 0.63414634 0.19781051 1971.02.22 41700 1973.02.24 8900 53 1.54716981 0.64634146 0.18953798 1972.07.27 9160 1903.06.03 8680 54 1.51851852 0.65853659 0.18142009 1973.02.24 8900 1943.06.14 8570 55 1.49090909 0.67073171 0.17345116 1974.05.19 19200 1934.06.09 8040 56 1.46428571 0.68292683 0.16562583 1975.06.22 10800 1902.07.10 7780 57 1.43859649 0.69512195 0.157939 1976.05.24 6050 1939.03.13 7500 58 1.4137931 0.70731707 0.15038586 1977.05.28 9200 1938.07.08 7450 59 1.38983051 0.7195122 0.14296184 1978.03.21 38900 1963.06.25 7350 60 1.36666667 0.73170732 0.1356626 1979.03.18 14800 1941.03.10 7080 61 1.3442623 0.74390244 0.12848402 1980.05.28 12200 1933.07.13 7020 62 1.32258065 0.75609756 0.12142216 1981.06.15 5100 1956.06.26 6960 63 1.3015873 0.76829268 0.1144733 1982.05.20 19800 1929.06.19 6620 64 1.28125 0.7804878 0.10763388 1983.06.19 18700 1968.06.26 6540 65 1.26153846 0.79268293 0.1009005 1984.06.18 43100 1942.06.20 6500 66 1.24242424 0.80487805 0.09426992 1985.04.27 18500 1955.07.14 6440 67 1.2238806 0.81707317 0.08773905 1986.03.19 20500 1988.09.29 6210 68 1.20588235 0.82926829 0.08130494 1987.03.25 23500 1976.05.24 6050 69 1.1884058 0.84146341 0.07496476 1988.09.29 6210 1914.06.16 5750 70 1.17142857 0.85365854 0.06871581 1989.09.05 9950 1913.05.18 5740 71 1.15492958 0.86585366 0.0625555 1990.06.19 37000 1946.02.07 5720 72 1.13888889 0.87804878 0.05648136 1991.06.06 24300 1981.06.15 5100 73 1.12328767 0.8902439 0.05049099 1992.06.17 19100 1901.06.23 4740 74 1.10810811 0.90243902 0.04458213 1993.03.11 33500 1900.09.13 4160 75 1.09333333 0.91463415 0.03875259 1994.03.06 15800 1899.06.13 3700 76 1.07894737 0.92682927 0.03300026 1995.05.29 23400 1911.06.30 3670 77 1.06493506 0.93902439 0.02732313 1996.08.07 35100 1970.04.22 3310 78 1.05128205 0.95121951 0.02171925 1997.06.21 20200 1935.06.06 3300 79 1.03797468 0.96341463 0.01618676 1998.09.06 26300 1937.06.20 2920 80 1.025 0.97560976 0.01072387 1999.08.08 27300 1930.11.21 2120 81 1.01234568 0.98780488 0.00532883

The plot below and best-fit line were generated using the following steps.

• A scatter plot of log R.I. as x and discharge as y was created.
• The Chart option of line fit was used to calculate the best line fit.

Note that the r-squared value of .93 would be considered somewhat reliable. Further knowledge of SEE would be useful here. Note the one potentially outlier value that is exerting a fair bit of control on the line position.

What does it mean? How can it be used?

• It can be used to estimate the probability of a certain size flood, and it can be used to estimate the size of a flood of a certain recurrence interval.
• A basic assumption is constancy of behavior thorough time. This requires careful thought and perhaps further analysis (e.g. times series analysis).
• If you are extrapolating past the length of the data (100 or 200 year flood), how far is it safe to extrapolate?