+ Reply to Thread
Results 1 to 7 of 7

Averageif rolling average with filtered cells

  1. #1
    Registered User
    Join Date
    02-29-2020
    Location
    Us
    MS-Off Ver
    2017
    Posts
    4

    Averageif rolling average with filtered cells

    I'm trying to find the 30 day average from a table of data with filtered cells. I originally had a my formulas setup with raw data not in a table format. The formulas worked beautifully until I changed it up and created tables from a master list of data for multiple types of queries. Once I did this I ran into hidden/filtered cells giving me inaccurate results. I have a column of dates and a column of numbers which are gathered from the difference between two dates. I'm looking for the average from the last 30 days. Here is the original formula I was using =AVERAGEIFS(!K$1:K$117,!E$1:E$117,">="&TODAY ()-30) where column K Has the data and column E has the dates. I believe I need to incorporate subtotal and offset into my formula to ignore hidden cells, but I can't seem to get it to work with the rolling 30 criteria. I have spent all day searching Google to point me in the right direction but I cannot get it to work. Can somebody please assist? Thank you

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,808

    Re: Averageif rolling average with filtered cells

    It's always best to include a sample file when posting a question (see the yellow/gold banner at the top of this page). You should be able to use the same formula, but just changed out with the table reference columns instead of hard-coded cell ranges.

  3. #3
    Registered User
    Join Date
    02-29-2020
    Location
    Us
    MS-Off Ver
    2017
    Posts
    4

    Re: Averageif rolling average with filtered cells

    Here is a sample
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,808

    Re: Averageif rolling average with filtered cells

    Hmm, the spreadsheet you uploaded doesn't seem to match your post. Your post references a 30-day average and none of your formulas that I see reference 30 days (either 90 or 120 days). Then your formula in your post refers to columns and your file has nothing in either column.
    So are you looking to average the "Difference of Days". And if so, which date should be within 30 days, date1 or date2?
    Lastly, when using a table it's usually not good practice to have all those blank lines.

    Any, if I understand correctly, and using date2 as the day within 30 days of today, you should be able to use this formula:
    =AVERAGEIFS(RMA_resolution_data[difference of days],RMA_resolution_data[date2],">="&TODAY()-30)

  5. #5
    Registered User
    Join Date
    02-29-2020
    Location
    Us
    MS-Off Ver
    2017
    Posts
    4

    Re: Averageif rolling average with filtered cells

    I grabbed a small snippet of my data that was over 30 days out and had to improvise and change the formula to 120 days out so that it would show a number which is highlighted in red. I made a quick sample of what I originally had (column G, H, and I) and then the table is a small portion of what I am currently working with (the extra lines are data I removed to get out of the way for this sample). The formula you provided is basically what I was originally using, but it does not work with tables with filtered cells as it does not exclude the filtered cells which is where I believe subtotal comes in. I just can't seem to get it to work with it with the rolling average criteria. Column C is the column i'm looking to get an average of from the last # of days from column A. Column F has a few formulas I was trying to get to work that are not panning out. Hope this helps decipher my quick sample I threw out.

  6. #6
    Registered User
    Join Date
    02-29-2020
    Location
    Us
    MS-Off Ver
    2017
    Posts
    4

    Re: Averageif rolling average with filtered cells

    Is there nobody that can assist with this? I still cannot figure this out. Thank you

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Averageif rolling average with filtered cells

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new here, I will provide the link for you this time.)

    https://www.mrexcel.com/board/thread...table.1126033/
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. Wieghted average of filtered cells
    By vineetpatel321 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-15-2018, 12:33 AM
  2. Sumproduct W/Offset to Create a Rolling Average for Filtered Cells
    By gtnewberry in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-05-2017, 11:41 AM
  3. Exclude 'total' cells for rolling sum/average
    By MrsRobot in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-17-2017, 09:30 AM
  4. COUNTIF and AVERAGEIF from filtered Data (Still Confused)
    By Buzz1126 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-12-2016, 02:52 PM
  5. [SOLVED] Rolling Average when Unhiding Cells
    By dianaschar in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-04-2013, 06:50 AM
  6. Replies: 1
    Last Post: 02-18-2012, 12:33 AM
  7. [SOLVED] average of visible cells in a filtered range
    By dave roth in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-23-2005, 08:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1