+ Reply to Thread
Results 1 to 8 of 8

converting hourly rainfall data into a daily rainfall sum

  1. #1
    Registered User
    Join Date
    02-12-2019
    Location
    Tehran
    MS-Off Ver
    2010
    Posts
    6

    converting hourly rainfall data into a daily rainfall sum

    hi

    Here is the file I'm working on.
    how can i convert hourly rainfall to daily rainfall in each station acc to "station_i" & "time" & "hourly rainfall" columns?


    Thanks a lot for any assistance which you might be able to offer.

    behnam
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: converting hourly rainfall data into a daily rainfall sum

    Power Query maybe?
    Maybe add a new column INT() date, group by that.
    Change your format for column C to Number with one decimal place.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: converting hourly rainfall data into a daily rainfall sum

    As you only have three different stations, you can manually list these in cells H1: J1.

    You can find the earliest date by using this formula in G2:

    =INT(MIN($B:$B))

    and then get consecutive dates using this in G3:

    =G2+1

    Format both cells as date using the style you prefer, then copy G3 down as far as you need to.

    Then you can use this formula in H2:

    =SUMIFS($D:$D,$A:$A,H$1,$B:$B,">="&$G2,$B:$B,"<"&$G2+1)

    Copy this across into I2 and J2, then copy down as required. I've shown the first month in the attached file.

    Hope this helps.

    Pete
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-12-2019
    Location
    Tehran
    MS-Off Ver
    2010
    Posts
    6

    Re: converting hourly rainfall data into a daily rainfall sum

    Dear Pete

    Thank you so much for your excellent guidance in solving the problem.

    I want to tell you that there are 77 stations in this file(year:1968) but because of xls file size limitation, I coukd not attach that big file and only 3 stations of this file were presented...

    in addition, I have about 50 xls files(year:1968 until 2017) that in each file(year) we have 77 stations...

    for example:

    1968.xls
    station 1
    station 2
    ...
    station 77
    1969.xls
    station 1
    station 2
    ...
    station 77
    1970.xls
    station 1
    station 2
    ...
    station 77
    .
    .
    .
    2017.xls
    station 1
    station 2
    ...
    station 77

    Whats your suggestion in this case?

    How do I automatically collect each station time series in a xls sheet column (same as your above solution) but from 1968 to 2017?
    Attached Files Attached Files
    Last edited by behnam1800; 02-18-2019 at 03:54 PM.

  5. #5
    Registered User
    Join Date
    02-12-2019
    Location
    Tehran
    MS-Off Ver
    2010
    Posts
    6

    Re: converting hourly rainfall data into a daily rainfall sum

    hi pete

    Thank you so much for your excellent guidance.

    how can i arrange these three "station id"s that are in column 1, in cells H1: J1 with formula not manually ?

    behanm

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: converting hourly rainfall data into a daily rainfall sum

    You can put this formula in cell E2 of the file that I returned to you in Post #3:

    =IF(COUNTIF(A$2:A2,A2)=1,MAX(E$1:E1)+1,"-")

    Copy this down to the bottom of your data. Then in H1 you can use this formula:

    =IFERROR(INDEX($A:$A,MATCH(COLUMNS($H:H),$E:$E,0)),"")

    Copy this across as far as you need to.

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    02-12-2019
    Location
    Tehran
    MS-Off Ver
    2010
    Posts
    6

    Re: converting hourly rainfall data into a daily rainfall sum

    Thanks so much for the unique solution.
    that was perfect.

    Behnam

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: converting hourly rainfall data into a daily rainfall sum

    Glad to help - thanks for the rep.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 01-09-2017, 06:22 PM
  2. how to convert minute rainfall to daily rainfall with missing value?
    By Ehsan83 in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 09-30-2016, 07:32 PM
  3. Replies: 1
    Last Post: 10-23-2015, 07:20 PM
  4. Convert rainfall data from 10 minutes to 20 min,30 min,1 hr ..
    By hussein f in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-23-2015, 05:28 PM
  5. How can average daily rainfall data at a time?
    By nischithav in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-17-2014, 01:21 AM
  6. Replies: 2
    Last Post: 09-05-2012, 07:55 AM
  7. Counting Rainfall Data
    By TightIsobars in forum Excel General
    Replies: 2
    Last Post: 01-17-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1