EarthLabs > Hurricanes > Lab 3: Putting Hurricanes on the Calendar > 3B: Counting Storms

Putting Hurricanes on the Calendar

Part B: Counting Storms to Define Hurricane Season

To find out when tropical storms and hurricanes occur, you'll use spreadsheet functions to tally and graph the number of days during each month that had storms reported from 1851 through 2007.

Stop & Think

1: In order to figure out which months of the year are most likely to experience hurricanes, should you look at the date on the header line for each storm (the first date that the storm was reported), or consider the daily data that list all dates that the storm was active? Whichever you choose, explain why your choice is better than the alternative.

Prepare the data

  1. On the HURDAT data page, select all and copy, then open a new spreadsheet document and paste. Note that instructions in this activity refer to menu items and commands in Microsoft Excel. Most other spreadsheet applications will have similar commands available.
    1. In the browser window that is displaying the HURDAT data, choose Edit > Select All then Edit > Copy.
    2. Start Excel and open a blank worksheet.
    3. Choose Edit > Paste
  2. Select Column A of your spreadsheet, then choose Data > Text to Columns
  3. In Step 1 of 3, choose Fixed Width.
  4. In Step 2 of 3, set the line breaks as shown here. Follow the directions in the Convert Text to Columns window to manage the break lines.
    Set column 2 for MM/DD

    An enlarged view of the column width settings
  5. In Step 3, select the column that contains the slashes by clicking its header, then click the Do not import column (Skip) option: isolating the month and date values from the slash symbol will allow you to avoid the complexity of dealing with date formats in Excel. Leave all other columns set to General formatting. Click the Finish button.
    Settings to skip column of slashes
  6. Sort the data by Column D to isolate the header rows from the daily data. Scroll down to where the header rows are clustered then select and delete them.

    The header rows only indicate the first date of each storm, not all the days that it was occuring. Therefore, the daily data offers more information about when hurricanes occur.

    Note also that the date that appears in the header row of each storm is repeated on the line below it. That makes it necessary to delete the header rows, or those dates would be counted twice for each storm.

    Because all the header rows still have a slash "/" as the initial character in Column D, you can sort the spreadsheet according to that column, and the result will be to group those rows together.
    • Click and drag across all the column headers to select them then choose Data > Sort...
    • In the drop down menu, choose Column D. Choose the Ascending radio button, and click OK.
    • Scroll down to where values in column D begin with a slash followed by the year. Recall that these header rows also contain the storm names. It's perfectly OK to delete these for this particular analysis as you are only interested in storm dates.
    • Click and drag along the row numbers to select all the header rows then choose Edit > Delete.
  7. Select all columns again and choose Data > Sort. Sort by Column B so all the daily storm reports are listed from January through December. Scroll through the data to see if there are any months that have not had tropical storms or hurricanes. Scroll all the way to the final daily storm date to see how many rows of your spreadsheet show daily storm data.
    If necessary, download the data in a prepared .xls file (Excel 2MB Aug5 08). Once you open the file, hover your cursor over cell A1 for a description of the data in each section.

    Checking In Questions

    Take a quick look at the data: which months seem to have the fewest tropical storms?
    February and March
    What other information is shown in column B, below the daily data in your spreadsheet?
    The maximum intensity that each storm reached: HR (Hurricane), TS (Tropical Storm), or SS (Subtropical Storm)

Count the Storms

In order to count the number of times storms have been reported in each month, you'll use Excel's COUNTIF command. Essentially, you'll be telling Excel which cells to check and what condition to check for. NOTE: As the HURDAT database is always growing, these examples show how to count storms in up to 15,000 rows of data.

  1. Choose an open place in your spreadsheet (at the top of column I, for instance) and make a column that lists the months from January through December.
  2. Visual explanation of CountIf command
  3. In the cell to the right of "January," enter this "Count If" formula. The result of the formula will be the total number of days in January (month = 1) with active storms since 1851. Be sure that you include the intial equal sign (=) in your formula.
  4. Type appropriate COUNTIF formulas into the next 11 cells to count storm days in February through December. Keep in mind that for each formula, you specify the range of cells to check (from B1 through B15000) and the condition you are checking for (months 2 through 12, one at a time). Don't forget to include an equal sign at the beginning of each formula.
  5. =COUNTIF(B1:B15000,"1")
    and so on, through =COUNTIF(B1:B15000,"12")
  6. Once you've entered the formulas correctly, the cell will show the number of storm days that have been reported for each month since record keeping began.

Checking In Questions

  • Which months have had the most tropical storms and hurricanes reported?
    September; Over 4000 dates in September have had active storms from 1851 through 2006.
  • Which month has had the fewest reported storms?
    Both February and March have had only 4 dates when storms were reported.

Graph the Number of Storms by Month

A graph of the HURDAT storms by month will give you a clear visual comparison of the data so you can put hurricane season on the calendar.
  1. Make another column listing the months from January through December.
  2. Copy the cells that show the number of storms and use Edit > Paste Special to put their Values into a new column to the right of your new Months column.
  3. Use the chart wizard to make a clustered column graph showing the number of storms by month. Give the graph and its axes appropriate titles.

    Stop & Think

    2: What would you recommend as the official starting and stopping dates for hurricane season? Describe your reasoning. How would you respond to someone who thought that all year long should be considered hurricane season?
    3: Write a set of COUNTIF formulas that will tell you how many of the HURDAT storms were hurricanes (HR), tropical storms (TS), and subtropical storms (SS). Make a pie chart to compare the numbers of each.