# exclude zero from subtotaled average when filtered

1. ## 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. ## Re: exclude zero from subtotaled average when filtered

Possibly:-

Formula:
`Please Login or Register  to view this content.`

3. ## Re: exclude zero from subtotaled average when filtered

Formula:
`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:
`Please Login or Register  to view this content.`

or
Formula:
`Please Login or Register  to view this content.`

4. ## Re: exclude zero from subtotaled average when filtered

Try this
Formula:
`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

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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