+ Reply to Thread
Results 1 to 4 of 4

exclude zero from subtotaled average when filtered

  1. #1
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    365 (15.0.5501.1000)
    Posts
    534

    exclude zero from subtotaled average when filtered

    I have some data in column F that I want to average. The data needs to be filtered so that I can remove some of the data and change the average.

    =subtotal(1,F3:f20) works great but it includes the zeros and is bringing the average down. How can I easily tell the formula to remove all zeros and still be able to filter the data?

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: exclude zero from subtotaled average when filtered

    Possibly:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: exclude zero from subtotaled average when filtered

    Your formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Will include in it the filtered out rows in its calculations. 1=average including hidden 101=average excluding filtered.

    So if you actually need 101, not including filtered out items in your average, the simple answer is to also filter out the 0 values. If you instead need to get the average despite the filter, and want to exclude the 0 values then subtotal likely isnt the best option for you.

    In that case you could use AVERAGEIF to exclude 0 from your averages. You could also manually calculate the averages by dividing a sum/count and only count cells that are not 0 (summing the 0 values doesnt matter as anything +0 is still the same number).

    Something like:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: exclude zero from subtotaled average when filtered

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v F G H
    1
    2
    3 0 29.92
    4 46
    5 0
    6 0
    7 24
    8 25
    9 0
    10 24
    11 47
    12 0
    13 44
    14 36
    15 27
    16 34
    17 0
    18 39
    19 9
    20 4
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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. [SOLVED] Filtered data to exclude header row
    By Sgligori in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-11-2016, 04:07 PM
  2. [SOLVED] Exclude Filtered Rows in SUBTOTAL
    By callie64 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-10-2016, 03:34 PM
  3. [SOLVED] Average Percentage (weighted average) but I want to exclude N/A
    By mespinoza in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 12-28-2015, 02:53 PM
  4. [SOLVED] Count a filtered list but exclude the zeros
    By clarcombe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-25-2014, 04:14 AM
  5. Count values in column but exclude filtered rows
    By elee532 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-26-2013, 12:53 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