+ Reply to Thread
Results 1 to 4 of 4

Thread: Analyzing 311 Calls trend

  1. #1
    Points: 22, Level: 1
    Level completed: 43%, Points required for next Level: 28

    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Analyzing 311 Calls trend




    For an assignment, I am analyzing 311 call trends. I have downloaded the total number of 311 calls made in my city per month for the last five years and trying to project the remaining number of 311 calls I expect to be made in 2014. 311 calls tend to peak, at least from what I've observed, from Oct-Mar, and then fall off. The activity also tends to increase year-to-year. I am working on this in Excel and can show the trends in line graphs but what statistical functions in Excel can I use to quantify the trend? And then how do I actually make a projection? It doesn't have to be anything too precise. I just need something.

  2. #2
    TS Contributor
    Points: 14,811, Level: 78
    Level completed: 91%, Points required for next Level: 39
    Miner's Avatar
    Location
    Greater Milwaukee area
    Posts
    1,171
    Thanks
    34
    Thanked 405 Times in 363 Posts

    Re: Analyzing 311 Calls trend

    This sounds like a time series analysis with a trend and seasonality. I don't think Excel has any built-in functionality to support this beyond trending and regression, which cannot handle the seasonality.

  3. #3
    Points: 22, Level: 1
    Level completed: 43%, Points required for next Level: 28

    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analyzing 311 Calls trend

    Quote Originally Posted by Miner View Post
    This sounds like a time series analysis with a trend and seasonality. I don't think Excel has any built-in functionality to support this beyond trending and regression, which cannot handle the seasonality.
    So how would you do that in Excel? I see regression under the Data Analysis add-in. But what would the "Input Y-Range" and "Input X-Range" be?

  4. #4
    Fortran must die
    Points: 58,790, Level: 100
    Level completed: 0%, Points required for next Level: 0
    noetsi's Avatar
    Posts
    6,532
    Thanks
    692
    Thanked 915 Times in 874 Posts

    Re: Analyzing 311 Calls trend


    The best answer is not to do it in excel. You can create dummy variables for season (11 for months 3 for quarterly data) and a trend variable and run linear regression but if you have autocorrelation (which is very likely) your p values will probably be wrong.

    Time series is simply too complex to be handled by excel which does basic stats.

    It is possible to deseasonalize and detrend data an then analze that. I am unsure if you can do this in excel.
    "Very few theories have been abandoned because they were found to be invalid on the basis of empirical evidence...." Spanos, 1995

+ Reply to Thread

           




Posting Permissions

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






Advertise on Talk Stats