+ Reply to Thread
Results 1 to 3 of 3

Calculate average weekly, monthly, quarterly and annually?

  1. #1
    Registered User
    Join Date
    12-09-2014
    Location
    Germany
    MS-Off Ver
    Excel 2013
    Posts
    2

    Question Calculate average weekly, monthly, quarterly and annually?

    Hi,

    I am doing a Twitter analysis and I have the data in .csv format. In that I have date_published and sentiment_score two column along with some columns. But these two are now important for my average extraction.
    I have to find out the average of the sentiment_score column ie from 2007 to 2011 weekly, monthly, quarterly and annually. The problem here is the dates are irregular, i.e consider first week of January 2007. 01/01/2007 to 07/01/2007 but it has 03/01/2007. And for some weeks one day is missing for few 3 days are missing and for few weeks on the same day there are 4 tweets. So how can I calculate the average for this. I posted an example and attached a sample data set. I am really stuck at this point as doing manually will take a lot of time at least for weekly and monthly for 5 years long data. If any one knows any solution or can help me it will be really helpful.


    Example posted below:
    Date_published
    03-01-07
    12-01-07
    15-01-07
    15-01-07
    16-01-07
    16-01-07
    15-01-07
    17-01-07
    17-01-07
    17-01-07
    18-01-07
    19-01-07
    20-01-07
    22-01-07
    22-01-07
    26-01-07
    27-01-07
    29-01-07
    31-01-07
    31-01-07
    31-01-07
    30-01-07
    02-02-07
    02-02-07
    02-02-07
    02-02-07
    02-02-07
    02-02-07
    02-02-07
    03-02-07

    Thanks,

    Karthik
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Calculate average weekly, monthly, quarterly and annually?

    Sure, first we scrub the dates to make them into something we can use.

    I went with F2:
    =IF(ISNUMBER(D2),D2,(MID(D2,4,2)&"/"&LEFT(D2,2)&"/"&RIGHT(D2,2))*1)

    This formula might need tweaked depending on your regional time date stuff.

    G2:
    =WEEKNUM(F2,1)

    H2:
    =TEXT(F2,"mmmm")

    I2:
    =YEAR(F2)


    Copy all four cells down to 3954.

    Select E1:I3954, Insert -> Pivot Table

    Row Labels: Year, Month, WeekNum
    Values: Score

    When you drag Score into the values it will default to Sum. Right click it, Value Settings, change to Average


    I prefer old school Pivot Tables, so I also right click the table, Pivot Settings.. and change Display Setting to Classic Layout.
    Now you can drill down from Yearly, Monthly, and Weekly Averages for everything.
    Attached Files Attached Files
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    12-09-2014
    Location
    Germany
    MS-Off Ver
    Excel 2013
    Posts
    2

    Wink Re: Calculate average weekly, monthly, quarterly and annually?

    Hi,

    This looks awesome. I will try and let you know.

    Thanks a lot.

+ 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. Adding a Worksheet to work out my Weekly/Monthly/Quarterly/yearly income
    By DaleWinton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-15-2014, 08:21 PM
  2. [SOLVED] How do I group daily totals into weekly/monthly/quarterly totals
    By situationroom in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2013, 09:58 AM
  3. How to calculate monthly & quarterly reports
    By island_monkey in forum Excel General
    Replies: 7
    Last Post: 02-04-2009, 12:53 PM
  4. quarterly, semi-annually??
    By colonel-shoe in forum Excel General
    Replies: 5
    Last Post: 05-23-2006, 11:28 AM
  5. [SOLVED] List employees with hours worked, weekly, quarterly, and annually
    By Steve j. in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 02-18-2005, 10:06 AM

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