1. Welcome! Please take a few seconds to create your free account to post threads, make some friends, remove a few ads while surfing and much more. ClutchFans has been bringing fans together to talk Houston Sports since 1996. Join us!

Excel Help? Averages of Values over Variable Terms & Start Dates

Discussion in 'BBS Hangout' started by kpsta, Jun 8, 2011.

Tags:
  1. kpsta

    kpsta Member

    Joined:
    Sep 2, 2001
    Messages:
    2,654
    Likes Received:
    166
    I have a list of values that changes over time on a monthly or annual basis (for example June 2011 to May 2012 is $108, then June 2012 through May 2012 is $165, etc.) I need to be able to get an average of those values based on two inputs: term length in months and a start date. So, for example, I might have a 36 month term starting in September of 2011. How might I best go about organizing the data and building that formula?

    Thanks!
     
    #1 kpsta, Jun 8, 2011
  2. Lil Pun

    Lil Pun Member

    Joined:
    Oct 6, 1999
    Messages:
    34,143
    Likes Received:
    1,038
    Here is a forum dedicated to solving nothing but Excel problems. You may get a quicker response there.


    http://www.excelforum.com
     
    #2 Lil Pun, Jun 8, 2011
  3. kpsta

    kpsta Member

    Joined:
    Sep 2, 2001
    Messages:
    2,654
    Likes Received:
    166
    Yeah, I got nothing over there at that forum, but thanks Lil Pun. I figured we had a few Excel experts on here who might have some ideas.
     
    #3 kpsta, Jun 9, 2011
  4. Pete the Cheat

    Pete the Cheat Member

    Joined:
    Jun 30, 2006
    Messages:
    3,100
    Likes Received:
    487
    could you give an example of how the raw data is formatted? sounds doable, but you are most likely going to have to create some additional columns depending on how the data is formatted.
     
    #4 Pete the Cheat, Jun 9, 2011
  5. SwoLy-D

    SwoLy-D Member

    Joined:
    Jul 20, 2001
    Messages:
    37,618
    Likes Received:
    1,456
    Even though he's a cheat, I am with PETE on this. ;)

    Are they all on the same sheet or over different sheets, on the same column or spread across different columns, on the same row or different rows? I am asking this because it might be that you can just write a macro that takes the same cell location over different sheets (or even columns) given an amount of different entries, or in your case months. :cool:
     
    #5 SwoLy-D, Jun 9, 2011
  6. kpsta

    kpsta Member

    Joined:
    Sep 2, 2001
    Messages:
    2,654
    Likes Received:
    166
    I've got one workbook with two tabs at the moment...

    There's an input tab that allows the user to input the start date (always the 1st of a given month) and the term length in months.

    On the other tab (and I can rearrange the data as needed), I've got a row with the dates (6/1/2011, 7/1/2011, etc.), and then a row below that has the value for each month ($105 for a period of months, then $220 for another period, etc.). The values don't necessarily change each month (they may change every 12 months, but I figured I'd want to have a value per month since I'm dealing with different term lengths and variable start dates.

    And thanks... no one from the excel forums had responded yet.
     
    #6 kpsta, Jun 10, 2011
(You must log in or sign up to post here.)

Share This Page