converting hourly rainfall data into a daily rainfall sum

[behnam1800]

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

[Special-K]

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.

[Pete_UK]

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

[behnam1800]

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?

[behnam1800]

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

[Pete_UK]

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

[behnam1800]

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

Behnam

[Pete_UK]

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