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.

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.

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?


Copyright by Harmon D. Maher Jr.. This material may be used for non-profit educational purposes if proper attribution is given. Otherwise please contact Harmon D. Maher Jr