+ Reply to Thread
Results 1 to 12 of 12

Average only visible (filtered) data that meets criteria.

  1. #1
    Registered User
    Join Date
    12-21-2006
    Posts
    32

    Average only visible (filtered) data that meets criteria.

    I have a summary table of agents that references monthly data that is filterable. I need an average formula for each agent in the summary table that would show me the average of the data dependent upon how the reference data is filtered & if data doesn’t appear for an agent, because they’re filtered out, it would show “n/a” when not.

    Below I’ve shown the average, per agent of the unfiltered data in B8:B16.
    If filtered to only show Billy Bob in Jan & Feb … Billy Bob would show an average of 7 & John Boy & Mary Sue would show “n/a”.
    If filtered to only show Jan & Feb … Billy Bob would be 7
    John Boy would be 9
    Mary Sue would be 11

    A B C
    1 Agent Average
    2 Billy Bob 12
    3 John Boy 14
    4 Mary Sue 16
    5
    6
    7 Name Range Month Sum Range
    8 Billy Bob Jan 2
    9 John Boy Jan 4
    10 Mary Sue Jan 6
    11 Billy Bob Feb 12
    12 John Boy Feb 14
    13 Mary Sue Feb 16
    14 Billy Bob Mar 22
    15 John Boy Mar 24
    16 Mary Sue Mar 26

  2. #2
    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
    52,917

    Re: Average only visible (filtered) data that meets criteria.

    take a look at using =subtotal(101 for this. Using 101 will average only visible rows.
    Or, if there is some logic or rule as to why some rows are hidden, you might be able to use that in an =averageifS() formula

    If you still have a problem, I suggest you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    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

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Average only visible (filtered) data that meets criteria.

    Try this...

    Data Range
    A
    B
    C
    1
    Agent
    Average
    Month(s)
    2
    Billy Bob
    7
    Jan
    3
    John Boy
    9
    Feb
    4
    Mary Sue
    11
    5
    6
    7
    Name
    Month
    Sum
    8
    Billy Bob
    Jan
    2
    9
    John Boy
    Jan
    4
    10
    Mary Sue
    Jan
    6
    11
    Billy Bob
    Feb
    12
    12
    John Boy
    Feb
    14
    13
    Mary Sue
    Feb
    16
    14
    Billy Bob
    Mar
    22
    15
    John Boy
    Mar
    24
    16
    Mary Sue
    Mar
    26


    C2:C3 = months to average

    This array formula** entered in B2 and copied down:

    =IFERROR(AVERAGE(IF(SUBTOTAL(2,OFFSET(C$8,ROW(C$8:C$16)-ROW(C$8),0,1)),IF(A$8:A$16=A2,IF(ISNUMBER(MATCH(B$8:B$16,C$2:C$3,0)),C$8:C$16)))),"N/A")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    12-21-2006
    Posts
    32

    Re: Average only visible (filtered) data that meets criteria.

    Thanks for the posts.

    By itself Subtotal doesn't work because it isn't specific to an agent. Average, AverageIF or AverageIFS I don't think will work because it doesn't take into account filtered data.

    I was unable to download a sample file sorry.

    The Array Formula I ? as I do not know specifically how the information is going to be filtered so I can't specify months. The formula needs to be dynamic enough that it will automatically recalculate the agents average if the referenced data is filtered.

    I have a SUM formula that sums the data (see below), but I need to do the same thing for an average.

    Sales - Sum formula ... IF(SUMPRODUCT(SUBTOTAL(9,OFFSET($C$12,ROW($C$12:$C$20)-ROW($C$12),0)),($B$12:$B$20=B5)+0)=0,"No Data or Filtered",SUMPRODUCT(SUBTOTAL(9,OFFSET($C$12,ROW($C$12:$C$20)-ROW($C$12),0)),($B$12:$B$20=B5)+0))

  5. #5
    Registered User
    Join Date
    12-21-2006
    Posts
    32

    Re: Average only visible (filtered) data that meets criteria.

    AVERAGE.xlsx

    Sorry I figured out how to attach a sample file.

    I am using Excel 2010 by the way.

    In the attached example, Lindsy will always show "No Data or Filtered" because she's not in the Referenced data.

    I need to be able to filter the "Referenced Data" & show only the average for sales shown for a given agent in a Summary. If no sales data is visible if filtered than I need the formula result should be "No Data or Filtered".

    The formula needs to be dynamic enough to be able to calculate each agents average no matter what filter is applied to the Referenced Data.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Average only visible (filtered) data that meets criteria.

    Try this array formula** entered in D5 and copied down:

    =IFERROR(AVERAGE(IF(SUBTOTAL(2,OFFSET(C$18,ROW(C$18:C$26)-ROW(C$18),0,1)),IF(B$18:B$26=B5,C$18:C$26))),"No Data or Filtered")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Average only visible (filtered) data that meets criteria.

    Try this formula in a helper column in D18:D26.

    =--OR(SUBTOTAL(103,A18),SUBTOTAL(103,C18))

    Then for filtered sums this formula

    =SUMIF(D18:D26,1,C18:C26)

    and for filtered averages this formula

    =AVERAGEIF(D18:D26,1,C18:C26)
    Last edited by FlameRetired; 10-06-2015 at 02:02 AM.
    Dave

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Average only visible (filtered) data that meets criteria.

    Or still using the helper column (in D18:D26 as above) this in C5:C8
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and this in D5:D8
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 10-06-2015 at 03:56 AM.

  9. #9
    Registered User
    Join Date
    12-21-2006
    Posts
    32

    Re: Average only visible (filtered) data that meets criteria.

    Thanks for the input.

    Unfortunately I can't have any helper cells.

    Tony... I tried it & the result was always "No Data or Filtered" even thou it should have returned a result.

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Average only visible (filtered) data that meets criteria.

    =IF(SUMPRODUCT(SUBTOTAL(9,OFFSET($C$18,ROW($C$18:$C$26)-ROW($C$18),0)),($B$18:$B$26=B5)+0)=0,"No Data or Filtered",SUMPRODUCT(SUBTOTAL(9,OFFSET($C$18,ROW($C$18:$C$26)-ROW($C$18),0)),($B$18:$B$26=B5)+0)/SUMPRODUCT((SUBTOTAL(9,OFFSET($C$18,ROW($C$18:$C$26)-ROW($C$18),0))>0)+0,($B$18:$B$26=B5)+0))
    Please Login or Register  to view this content.
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  11. #11
    Registered User
    Join Date
    12-21-2006
    Posts
    32

    Re: Average only visible (filtered) data that meets criteria.

    Sorry Guys, my bad. Tony Valko's formula works. I forgot to hit Ctrl+Shift+Enter. I really appreciate everyones input.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Average only visible (filtered) data that meets criteria.

    Good deal. Thanks for the feedback!

+ 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. Copy Filtered Data on visible rows
    By jamesbrightwell in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-25-2015, 01:31 AM
  2. [SOLVED] Calculating average of column if row meets criteria
    By dvs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2015, 07:11 PM
  3. Offset to next visible cell in filtered data
    By Varun13 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-23-2013, 04:37 AM
  4. Error checking of visible filtered data - Display text in textbox if no data visible
    By cocobean in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-06-2012, 12:58 AM
  5. Average of dates and times if it meets a specific criteria
    By Shadoweski in forum Excel General
    Replies: 1
    Last Post: 09-28-2010, 10:26 AM
  6. average if meets criteria
    By gelandl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-12-2010, 03:22 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