+ Reply to Thread
Results 1 to 8 of 8

Help with 3 month rolling average

  1. 03-08-2015, 04:03 PM #1
    mikewild
    mikewild is offline
    Registered User
    Join Date
    03-08-2015
    Location
    vancouver canada
    MS-Off Ver
    2003
    Posts
    2

    Help with 3 month rolling average

    Hey there,
    I've been pulling my hair out searching the forums and trying to adapt formulas to work within my spreadsheet for a rolling three month average. If you look at the attached spreadsheet, I need to do this for several columns and rows.

    For example, in the attached spreadsheet, the rolling average worksheet needs to reference the particular agent on the data worksheet for the various criteria. The months on the data sheet go from oldest month at the top to current month at the bottom. I need to reference the three most current months. Sometimes the months will contain data, sometimes they won't. Each month a new row is inserted into the data worksheet and everything below that moves down a line. This snapshot is just for a few agents for the purposes of this post, but there are around 100 agents that appear on the data sheet in the original workbook. I think if I can get help with 1 agent and 1 column, I can implement the formula into the other cells where I need to do the same and simply edit the cell references.

    Any help would be greatly appreciated.

    rolling average.xls
    Reply With QuoteRegister To Reply
  2. 03-08-2015, 04:10 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,044

    Re: Help with 3 month rolling average

    Hi, welcome to the forum

    Before I start on this, a few suggestions/questions?...

    1. instead of using month names (Jan/Feb/etc), rather, use actual dates (1/1/14...2/1/14 etc)
    2. I dont think you need to keep repeating the month columns N and Z. You already have the months in B
    3. Can you add some Agent numbers?
    4. Are you really using 2003?
    Last edited by FDibbins; 03-08-2015 at 04:12 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 03-08-2015, 04:29 PM #3
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,219

    Re: Help with 3 month rolling average

    Hi mike and welcome to the forum,

    The structure of your data is easy to look at and understand but very hard to deal with in Excel. Excel is a TABLE based system where most of the tools and power comes from following its definition of TABLE. Read
    http://www.excel-easy.com/data-analysis/tables.html and
    http://www.techrepublic.com/blog/10-...-table-object/

    So you've asked a question that would be very easy if your data was structured as a table. What you have is a separate kind of table for each Agent. You should restructure your data so you have... Let me work on it a bit. Also note Excel 2013 has a much richer Pivot Table user interface.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  4. 03-08-2015, 04:56 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,044

    Re: Help with 3 month rolling average

    @ Marvin, OP's profile says 2003, not 2013?
    Reply With QuoteRegister To Reply
  5. 03-08-2015, 05:18 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,044

    Re: Help with 3 month rolling average

    I fully agree with Marvin that if your data was laid out differently, it would make for easier summary/analysis, but I also understand that this is not always possible.

    With that in mind, take a look at the attached.
    You only showed 3 "columns" of agents, so that is what this is based in. I used a helper column to try and keep the formulas simple(ish), and added a cell for todays date.

    Take a look and see what you think?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 03-08-2015, 09:12 PM #6
    mikewild
    mikewild is offline
    Registered User
    Join Date
    03-08-2015
    Location
    vancouver canada
    MS-Off Ver
    2003
    Posts
    2

    Re: Help with 3 month rolling average

    Thanks for the tips folks, sadly yes I'm still on excel 2003 and unfortunately I can't restructure the data into more of a table format. I liked the Pivot table, and I assume that it wouldn't work with the current layout of the data. @FDibbins, I think your provision would work, but I don't think it's referencing the data from the first worksheet. I'll try playing with the formula a bit to see if I can get it to work for my needs. Thanks again folks.
    Reply With QuoteRegister To Reply
  7. 03-08-2015, 10:18 PM #7
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,219

    Re: Help with 3 month rolling average

    I gave him an .xls answer but wanted him to know 2013 has more power/features.
    Reply With QuoteRegister To Reply
  8. 03-08-2015, 10:23 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,044

    Re: Help with 3 month rolling average

    Quote Originally Posted by mikewild View Post
    @FDibbins, I think your provision would work, but I don't think it's referencing the data from the first worksheet.
    My helper uses...
    =IFERROR(IFERROR(ADDRESS(MATCH(A2,data!$B$1:$B$500,0),2,,,"data"),ADDRESS(MATCH(A2,data!$N$1:$N$500,0),14,,,"data")),ADDRESS(MATCH(A2,data!$Z$1:$Z$500,0),26,,,"data"))

    And my extract formulas use this...
    =AVERAGE(OFFSET(INDIRECT($F2),MATCH(EOMONTH($E$1,-1)+1,data!$B$1:$B$47,0)-2,COLUMN(A1),-3,1))

    So it looks to me like it is? What makes you say that?

    @ Marvin, OK, gotcha now, I see what you meant (I read it as 2013 to begin with too lol)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need help about 12-month rolling average
    By chetora in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-13-2014, 10:53 AM
  2. 12 Month Rolling Average
    By JWPrecision in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-09-2013, 05:04 PM
  3. [SOLVED] 3 Month Rolling Average
    By tangomj in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-10-2013, 07:16 AM
  4. Rolling 12 month average
    By nhrav in forum Excel General
    Replies: 1
    Last Post: 08-22-2010, 11:43 AM
  5. 12 Month rolling average.
    By Glen Blaha in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2008, 09:20 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1