Need number of consecutive hours with temperatures below zero

  • I have a spread sheet of hourly temperatures which is broken down into year, month, day, time, and temperatures. I would like to find the top ten longest stretches of sub-zero temperatures and the time and date in which they started and ended. I have attached a spreadsheet for your perusal.


    Thanks in advance for any help you can provide.

  • Re: Need number of consecutive hours with temperatures below zero


    For time being, I could not post you the formulas that I used.
    This is the Top 10 in your list.


    Please refer the attachment to understand the method I used (note: I have kept first 500 in your list. You can paste remaining values in the sheet. and Copy paste the Formulas in column 'F', 'G' & 'H' till end of your list. In column 'H' filter choose '1's and in Column 'G' filter choose top 10 highest values.


    1996 1 6 17 -1
    1996 1 7 3 -1
    1996 1 19 21 -1
    1996 1 20 18 -2
    1996 1 25 14 -1
    1996 1 28 3 -1
    1996 2 4 21 -1
    1996 2 5 7 -1
    1996 2 6 12 -2
    1996 3 3 10 -1
    1996 12 20 7 -2
    1996 12 25 23 -2
    1996 12 26 8 -2
    1996 12 27 10 -2

  • Re: Need number of consecutive hours with temperatures below zero


    Thanks for the response! I actually needed the number of hours below zero along with start and end dates. I looked at the spread sheet and this did not catch the long stretch of 143 hours from 1/29/1996 through 2/4/1996.


    Jeff

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!