Hurricane Floyd hit North Carolina in September 1999. Here is a brief history of Hurricane Floyd (source: NOAA Coastal Services Center website, http://www.csc.noaa.gov/):

"On September 2, 1999, Hurricane Floyd formed from a tropical wave off the coast of Africa. By September 10, Floyd turned from a westward to a northwest course and its intensification trend temporarily halted. However, as Floyd turned back to the west it strengthened into a major hurricane and then to a strong category four hurricane on the Saffir/Simpson scale with 155 mile per hour (mph) winds. On September 13 and 14, Floyd ravaged portions of the central and northwest Bahamas. On September 16, Floyd turned to a northwest and north course while slowly weakening, eventually making landfall near Cape Fear North Carolina as a category two hurricane around 2:30 a.m. Eastern Daylight Time (EDT). The hurricane moved over the eastern part of North Carolina and accelerated north-northeast up the coast as it weakened."

Fifty seven deaths were directly attributable to Floyd, and reported insured losses totaled 1.325 billion dollars. Ordinarily, this figure would be doubled to estimate the total damage. However, in comparison to most hurricane landfalls, in the case of Floyd there was an inordinately large amount of freshwater flood damage, which probably alters the 2-to-1 damage ratio. Total damage estimates range from 3 to more than 6 billion dollars (source: Preliminary Report, Hurricane Floyd, http://www.nhc.noaa.gov/1999floyd.html).

You can estimate streamflow for any day that you have temperature and precipitation data. You currently have temperature and precipitation data for September 1999. You used some of those data in estimating the streamflow. Find these temperature and precipitation values in your original spreadsheet for station 02089000.  Create a new worksheet in your spreadsheet with the Insert Worksheet command.  Then copy the data for September.Because the 10-day rainfall column is based on a formula, you need to paste the data into the new worksheet without the formula.  After you have started copy with the Edit Copy command, switch to the new worksheet, put the cursor in cell A2 and then use the Edit Paste Special command.  You will see a dialog box that asks you what you want to paste.  Select the values option.  You can then copy the column headings from the original sheet into row 1 of the new worksheet to help you remember what each column represents.

To produce a flooding estimate, you need to add items to the spreadsheet to solve for gage height. Recall that you have two equations that you need to solve.  First, you need to fill in the values from your multiple regression equation to solve for the streamflow on each day in September.  Next, you need to use that streamflow in the equation you produced for the streamflow-gage height relationship to solve for gage height.  You then need to compare the estimated gage height with the gage height at flood stage, which you found in the first part of the exercise.  That height (flood stage) is 14 feet for this site.

To the right of your data, create column heads for the different steps in this solution.  The columns would be:

LN(Streamflow)           Streamflow           Gage Height       Flood Height


The equation in the LN(Streamflow) column will be taken from the regression results.  Start the formula with the = sign.  Then click on the regress1 worksheet tab so that you can see the regression results.  The first item in the equation will be the Intercept constant for the equation.  Type that number into your formula followed by the + sign.  The second equation item will be the coefficient for Winter * value for winter.  Copy in the number for the Winter coefficient followed by the * symbol to represent multiplication.  Then click on the tab to go back to your new worksheet (probably called Sheet2) and point to the first value in the Winter column followed by another + sign. Now add the other coefficients and values for Spring, Summer, 10dayPrec, AvrTemp in the same way you did for Winter.  Hit Enter to complete the formula. Your final equation should look something like this (we have substituted symbols for the real coefficient values):

=Intercept+Winter Coef.*Sheet2!K2+Spring Coef.*Sheet2!L2+Summer Coef*Sheet2!M2+10dayPrec Coef.*Sheet2!N2+AvrTemp Coef.*Sheet2!O2 

For the second column, you need to change back the natural logarithm of the streamflow to the real value.  The expoential function does this.  In that cell, enter the formula  =EXP(Q2) where Q2 should be the item in your LN(Streamflow) column.

Now that you have the actual streamflow, you can use it to estimate the gage height using the polynomial equation you fitted on the Gageheightstreamflow spreadsheet.  It has the form:

= -Coefficient*(R2^2)+Coefficient*R2+Coefficient

Substitute the appropriate coefficients and enter this formula. The reference cell should be the cell with the actual streamflow in it.  Now you have the prediction of the gage height.  In the last column, enter the constant for the height at which flooding occurs (14).  Now copy these four cells into all of the cells for September, 1999.  You now have a prediction of when flooding occurred at this spot and how much flooding occurred.  To illustrate our findings, you can make a line graph showing the predicted gage height and the flooding gage height.

Compare your results with the description of Hurricane Floyd at http://www.nhc.noaa.gov/1999floyd.html.  Does your model appear to predict the flooding at times congruent with when the storm came to North Carolina (the map below shows the flooding conditions for the two stations you tested)?  Write a short report using your data and model results to describe the flooding and its relationship to the extreme rainfall from Hurricane Floyd.

Click here to see a larger version of this map.