+ Reply to Thread
Results 1 to 18 of 18

Converting a daily data to a monthly or yearly one

  1. #1
    Registered User
    Join Date
    05-21-2012
    Location
    Rima Majed
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    10

    Converting a daily data to a monthly or yearly one

    Hello,

    I have a large dataset of protests that happened on daily basis over a period of 10 years. And I have 41 variable for each day.

    How can I transform it from daily data to monthly data (automatically summing up the monthly total of each variable) ?

    Thank you!
    Rima

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Converting a daily data to a monthly or yearly one

    Hi Rima & Welcome to the Forum,

    Let's say your dates are in column A with values in column B.

    In F1 you have 1/1/2012 and F2 2/1/2012 and so forth down the row.

    In G1

    =SUMPRODUCT(--(TEXT(F1,"mmyyyy")=TEXT($A$1:$A$21,"mmyyyy")),$B$1:$B$21)

    If you wanted to sum up by the year

    =SUMPRODUCT(--(TEXT(F1,"yyyy")=TEXT($A$1:$A$21,"yyyy")),$B$1:$B$21)

    with 1/1/2012 in F1

    Adjust the dates in column F to match the years your data covers
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    05-21-2012
    Location
    Rima Majed
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    10

    Re: Converting a daily data to a monthly or yearly one

    Hello Jeffrey,

    Thank you so much for your reply, but it looks like I need further assistance.

    I have attached my dataset, I do have dates in column A and the 42 other variables in columns B, C, D, etc...

    Could you let me know how I can compile dates to transform them to monthly or yearly and at the same time have the total for all 42 variables?

    Cheers,
    Rima
    Attached Files Attached Files

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Converting a daily data to a monthly or yearly one

    A pivot table would work nice here with some grouping on the date.

    Does this work for you?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-21-2012
    Location
    Rima Majed
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    10

    Re: Converting a daily data to a monthly or yearly one

    It looks perfect! thank you! But the is a missing column (first one, Main Sunni), could you tell me how you did it? I could try to add the missing column

    Thanks again,
    Rima

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Converting a daily data to a monthly or yearly one

    Yes sorry about that. I noticed afterwards I had missed one of the variables.

    Put your cursor on the Pivot Table
    On the right side of the screen you should see the Pivot Table Field List open up
    If it does not open up >> Options tab >> Field list (far right)

    On the field list you will see fields to choose from
    Sunni Main is not checked but do not check it because it will be placed at the end of the list.
    If you want it in order, click and hold on Sunni Main and drag down to the Values box
    Place right before the first one in the list

    This should take care of it...

  7. #7
    Registered User
    Join Date
    05-21-2012
    Location
    Rima Majed
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    10

    Re: Converting a daily data to a monthly or yearly one

    It worked perfectly (under builder though not under options)! Thank you so much!

  8. #8
    Registered User
    Join Date
    05-21-2012
    Location
    Rima Majed
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    10

    Re: Converting a daily data to a monthly or yearly one

    Jeff, am sorry to bother you with this, but I need to change the dates grouping (instead of <12/01/2000, I want it to be < 01/01/2000), how can I do that?

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Converting a daily data to a monthly or yearly one

    Yes the terminology may be different as I do not have Excel for MAC...

    On the pivot table, right click on the date and you should get an option list with the option of group.

    In this group option you can modify starting dates....

    Unless you are referring to what you see in the date list.

    If this is the case, select the drop down on Row labels.

    Hopefully one of these helps

  10. #10
    Registered User
    Join Date
    05-21-2012
    Location
    Rima Majed
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    10

    Re: Converting a daily data to a monthly or yearly one

    Jeff, help! I deleted one row from my raw data and the grouping on the pivot table disappeared! I am trying to attach the file for you but it isn't working, can I send it to you on your email or stag? I am sorry about all the mess but I have to submit this tomorrow and I can't seem too understand how the grouping is created.

    Best,
    Rima

  11. #11
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Converting a daily data to a monthly or yearly one

    You could send it to me, but I won't be able to get to my email until after work. Another 5 or 6 hours from now.

    Why can't you attach it here?

    http://www.pivot-table.com/excel-piv...nths-and-weeks

    http://www.contextures.com/xlPivot07.html

  12. #12
    Registered User
    Join Date
    05-21-2012
    Location
    Rima Majed
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    10

    Re: Converting a daily data to a monthly or yearly one

    I don't know why it isn't attaching here. I will try to follow the steps on the links you've sent and will get back to you if it doesn't work.

    Thank you!
    Rima

  13. #13
    Registered User
    Join Date
    05-21-2012
    Location
    Rima Majed
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    10

    Re: Converting a daily data to a monthly or yearly one

    Perfect, it worked! So now it gets a bit more complicated. I have this pivot table but I need to create a table that has the dates and parties (Sunni Main, Sunni Other, etc...) vertically, and the reason of protest (pan-Arab, anti-Syria, socio-econ, etc...) horizontally (for now I can forget about other variables. So Variables in columns B to O I need to integrate with the dates and analyse it with respects to variables in columns P to Y. In other words, I should be able to see for example how many Sunni have protested for pan-Arab reasons in January 2000, Feb 2000 and so forth.

    Would you know how I could do that?
    P.S: I've attached the dataset! I think the previous one was too big.

    Best,
    Rima
    Attached Files Attached Files

  14. #14
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Converting a daily data to a monthly or yearly one

    I took a look at your attachment, but I can't really envision your requirement, or better yet, the layout you desire.

    Maybe put some thought into the layout and let's go from there...

    An example would help, not just a word description.

  15. #15
    Registered User
    Join Date
    05-21-2012
    Location
    Rima Majed
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    10

    Re: Converting a daily data to a monthly or yearly one

    Oups, I thought I had posted a reply earlier! Ok here is what I mean... I am looking for a table that looks like that (the nbrs are random):

    JAN 2000 Pan-Arab Pro-Syrian Anti-Syrian Socio-Economic Etc....
    - Sunni Main 2 0 0 1
    - Sunni Other 1 3 0 2
    - Shia Main 3 2 0 1
    - Christian Main 1 0 2 0
    - NGOs 0 0 0 2
    - Syndicats 0 0 0 5
    - Etc....

    FEB 2000 Pan-Arab Pro-Syrian Anti-Syrian Socio-Economic Etc....
    - Sunni Main 2 0 0 1
    - Sunni Other 1 3 0 2
    - Shia Main 3 2 0 1
    - Christian Main 1 0 2 0
    - NGOs 0 0 0 2
    - Syndicats 0 0 0 5
    - Etc...

    Would you know how I can do that?

    Cheers,
    Rima

  16. #16
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Converting a daily data to a monthly or yearly one

    Sorry I can't see the correlation here at all. I'm out of ideas so maybe somebody else well be able to pass along an idea.

  17. #17
    Registered User
    Join Date
    05-21-2012
    Location
    Rima Majed
    MS-Off Ver
    Excel 2010 for Mac
    Posts
    10

    Re: Converting a daily data to a monthly or yearly one

    It's ok, thank you so much for your help, much appreciated!

  18. #18
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Converting a daily data to a monthly or yearly one

    you can't have value fields in both row and column areas so your data won't work for that arrangement.
    btw, there's no such thing as Excel 2010 for Mac so I guess it's 2011?
    Josie

    if at first you don't succeed try doing it the way your wife told you to

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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