+ Reply to Thread
Results 1 to 9 of 9

Averaging Variable Number of Cells

  1. #1
    Registered User
    Join Date
    05-13-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    7

    Averaging Variable Number of Cells

    I'm looking to do a rolling average where the user enters x number of years and it averages over those x years over time. The data set looks as follows:

    Column A has dates from a to b in descending order (date b is first and date is a is last)
    Column B has data that corresponds to each date
    Column C should have a rolling average over time. For example if x is set to 1 the first entry should be the average of data points 1 to 365, the second entry should be the average of data points 2 to 366, etc. The problem is, I am unsure of how to get the range that is averaged to changed based on what x is set to. For example if x is set to 2, then it should be averaging data points 1 to 730.

    Any formula to help me achieve this would be great. Don't need the formula to be contained within a single column, I am open to using multiple columns to achieve this result.

    Many thanks!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,691

    Re: Averaging Variable Number of Cells

    It would help greatly if you posted your worksheet so that we can examine what you have and give you a specific response. Click on Reply to Thread, then click on the Go Advanced button and follow the wizard.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-13-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Averaging Variable Number of Cells

    I have attached an example. Column C755 has the first entry since I am taking a 3-year average as an example. I am looking to be able to change cell H1 which would dynamically change the number of years being averaged.

    Thank you all!
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,228

    Re: Averaging Variable Number of Cells

    Hi Max,

    Find the attached file which shows an easy Pivot Table answer. Hope this is what you wanted.
    Attached Files Attached Files
    Last edited by MarvinP; 05-26-2013 at 08:03 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    05-13-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Averaging Variable Number of Cells

    Thanks. Don't see any attached file, but will definitely take a look once posted. Also, I'm looking to avoid Pivot Tables. I was hoping to do it using formula(s) only.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,228

    Re: Averaging Variable Number of Cells

    Look now - I'm on a slow net today and it must have got lost in the ozone.

  7. #7
    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,047

    Re: Averaging Variable Number of Cells

    Hi and welcome to the forum

    I would suggest you update your profile - it says 2003, but your uploaded file is 2007 or later.

    Try this, copied from the bottom up...

    =AVERAGEIFS($B$1:$B$2111,$A$1:$A$2111,"<="&A2111,$A$1:$A$2111,">="&A2111-DATE($H$1,0,0))
    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

  8. #8
    Registered User
    Join Date
    05-13-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Averaging Variable Number of Cells

    Thank you so much! That formula is exactly what I need! Why copied from the bottom up? I can't just drag it down from the top?

  9. #9
    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,047

    Re: Averaging Variable Number of Cells

    Yes you could do that, I just thought you wouldnt want to bopther with x years from the start

+ 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