Lab: Esopus Creek, 100 year flood - Part 2

• Find the Coldbrook Station Esopus Creek station of the map (use the relief map and the on-line USGS map).
• Make a histogram of the maximum discharge rate of the Esopus Creek Are your data normally distributed?
• Not everything is lost. Perform a ln (to the base of e) transformation of the maximum flow rate data: Make a new column and calculate: =ln(max flow). Now make again a histogram of the transformed data. Are you approaching the normal distribution more closely?
• Now things will get a bit more complicated. Rank the data as discussed in class. You can do this by sorting the transformed data so that the maximum flow rate is at the top of the column. Use the SORT function in the DATA menu for this. In the next column put in the rank of each number by filling in a series of numbers from 1 to the number of years your record has. Use the FILL - SERIES option in the EDIT menu. Now calculate the `Exceedence probability Pe’ in the next column: =rank/(number of measurements+1). The probability is now not given in %. ‘1’ stands for 100% probability, ‘0.01’ means 1% probability. Pe gives you the probability that a particular flow rate is exceeded in the time period covered by the record.
• There are two ways to proceed from here
1. procedure
• Calculate mean and standard deviation of the logarithm of the maximum annual discharge rates.
• Determine the ln of the discharge rate that is exceeded in 1% of the cases using the NORMINV function and determine the equivalent discharge rate by  EXP LN(max flow rate)
• What is the flow rate in cf/s that you have to count on every 100 years?
2. procedure
• In the next column (title: inverse normal distribution) to the right put the following function: =NORMINV(Pe,0,1). This function is going to convert Pe into standard units (mean=0, SD=1). As the final step plot the LN(max flow rate) vs the standard units, you just calculated. If the data is normally distributed, the plot should show a straight line. Insert a trendline into the graph and include the equation describing the line.
• The 100y flood has an exceedence probability of 1%, or 0.01. On the x axis this value is equivalent to -2.326 standard units. This last value can also be calculated by the following equation: =NORMINV(0.01,0,1). Use the derived linear equation to determine the LN(max flow rate) and determine the flow rate by reversing the LN transformation: =EXP LN(max flow rate).
• What is the flow rate in cf/s that you have to count on every 100 years?

Source of data:

• Esopus Creek At Coldbrook Ny (01362500)
• EPA: Watershed Information Middle Hudson