Convert Daily Data to Monthly Data

[Ahmad]

Dear All,
Could anyone help me to slove this problem. I have an excel worksheet
data contain 4 column for meteorological daily data the first column
is year, the second column is month, the therd column is day and the
last column which is precipitation, so I want reshape my data from
daily to monthly, so the first column should be years, the second
january, the therd is february and the last one should be december.
Thank you

Dr: Ahmad dahamsheh

This is excemple from my worksheet
year month day prcp
1923 1 1 0.9
1923 1 2 0
1923 1 3 0
1923 1 4 0.9
1923 1 5 0
1923 1 6 0
1923 1 7 0
1923 1 8 0
1923 1 9 0
1923 1 10 0.5
I want to reshape my data to take this form

year jan feb mar ……. dec
1923 tpoj tpof tpom tpod
1924
1925
1926

tpoj=total precipitation of january
tpof=total precipitation of february
tpom=total precipitation of march
tpod=total precipitation of december

 

[Lars-Åke Aspelin]

Dear All,
Could anyone help me to slove this problem. I have an excel worksheet
data contain 4 column for meteorological daily data the first column
is year, the second column is month, the therd column is day and the
last column which is precipitation, so I want reshape my data from
daily to monthly, so the first column should be years, the second
january, the therd is february and the last one should be december.
Thank you

Dr: Ahmad dahamsheh

This is excemple from my worksheet
year month day prcp
1923 1 1 0.9
1923 1 2 0
1923 1 3 0
1923 1 4 0.9
1923 1 5 0
1923 1 6 0
1923 1 7 0
1923 1 8 0
1923 1 9 0
1923 1 10 0.5
I want to reshape my data to take this form

year jan feb mar ……. dec
1923 tpoj tpof tpom tpod
1924
1925
1926

tpoj=total precipitation of january
tpof=total precipitation of february
tpom=total precipitation of march
tpod=total precipitation of december

If you have your data in Sheet1 and the result should be at the top
left of another sheet, column A with the years and Row 1 with the
month names, try this:

In cell B2 of the other sheet:
=SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$B$2:$B$1000=COLUMN(B$1)-1)*Sheet1!$D$2:$D$1000)

Change the 1000 in all places to fit the size of your data in Sheet1.

Copy this formula across to cell M2 (the december column) and then
copy cells B2:M2 down as far as you have years in column A.

If you don't want to keep your original data in Sheet1 you can copy
the new data table and use Paste Special / Values to get rid of the
formulas and keep the data.

Hope this helps / Lars-Åke

 

[Ahmad]

If you have your data in Sheet1 and the result should be at the top
left of another sheet, column A with the years and Row 1 with the
month names, try this:

In cell B2 of the other sheet:  
=SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$B$2:$B$1000=COLUMN(B$1)-1)*S­heet1!$D$2:$D$1000)

Change the 1000 in all places to fit the size of your data in Sheet1.

Copy this formula across to cell M2 (the december column) and then
copy cells B2:M2 down as far as you have years in column A.

If you don't want to keep your original data in Sheet1 you can copy
the new data table and use Paste Special / Values to get rid of the
formulas and keep the data.

Hope this helps / Lars-Åke- Hide quoted text -

- Show quoted text -

Dear Lars-Ake
Thank you for your help, i cant handle your function, there is problem

This is my original data

year month day prcp
1923 1 1 0.9
1923 1 2 0
1923 1 3 0
1923 1 4 0.9
1923 1 5 0
1923 1 6 0
1923 1 7 0
1923 1 8 0
1923 1 9 0
1923 1 10 0.5
1923 1 11 0
1923 1 12 0
1923 1 13 0
1923 1 14 0


and i want this results

year jan feb mar ……. dec
1923 tpoj tpof tpom tpod
1924
1925
1926

where as
tpoj=total precipitation of january
tpof=total precipitation of february
tpom=total precipitation of march
tpod=total precipitation of december

 

[Lars-Åke Aspelin]

Dear Lars-Ake
Thank you for your help, i cant handle your function, there is problem

This is my original data

year month day prcp
1923 1 1 0.9
1923 1 2 0
1923 1 3 0
1923 1 4 0.9
1923 1 5 0
1923 1 6 0
1923 1 7 0
1923 1 8 0
1923 1 9 0
1923 1 10 0.5
1923 1 11 0
1923 1 12 0
1923 1 13 0
1923 1 14 0


and i want this results

year jan feb mar ……. dec
1923 tpoj tpof tpom tpod
1924
1925
1926

where as
tpoj=total precipitation of january
tpof=total precipitation of february
tpom=total precipitation of march
tpod=total precipitation of december

Try this:

=SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$B$2:$B$1000=COLUMN(B$1)-1)*Sheet1!$D$2:$D$1000)

( S-heet1 was a typo, should be Sheet1 )

Hope this helps / Lars-Åke