+ 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
    2010
    Posts
    134

    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
    5,702

    Re: exclude zero from subtotaled average when filtered

    Possibly:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Valued Forum Contributor
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,038

    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/2016
    Posts
    8,444

    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)

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