Average formula not working with Filtered Rows

frankee_gee

Board Regular
Joined
Mar 3, 2008
Messages
144
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Filtering Date Column "A" 1st qtr 2012<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
Column "C" has numbers where we figure the average of the entire column<o:p></o:p>
Formula: =AVERAGE(C36:C300)<o:p></o:p>
<o:p> </o:p>
When I filter or not the Date column "A", the Average is still the same.<o:p></o:p>
<o:p> </o:p>
How can I force it to only average out the rows displayed by the filter<o:p></o:p>
<o:p> </o:p>
Thanks for your time.<o:p></o:p>
Frankee
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Filtering Date Column "A" 1st qtr 2012<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Column "C" has numbers where we figure the average of the entire column<o:p></o:p>
Formula: =AVERAGE(C36:C300)<o:p></o:p>
<o:p></o:p>
When I filter or not the Date column "A", the Average is still the same.<o:p></o:p>
<o:p></o:p>
How can I force it to only average out the rows displayed by the filter<o:p></o:p>
<o:p></o:p>
Thanks for your time.<o:p></o:p>
Frankee
Try this...

=SUBTOTAL(1,C36:C300)
 
Upvote 0
Hi Frankee,

If you're using Excel 2007 or later and what an average of the visible (non filtered / hidden) row(s), use this:

=SUBTOTAL(101,C36:C300)

HTH

Robert
 
Upvote 0
thank you both. :) both segestions worked. :cool: now, i'm lookin at the whole book. two part question.

1.

With in the same sheet I'm trying to do the following


Column M: rows 36 thru 300 I'm now using this:
=SUBTOTAL(101,C36:L36)

Works Great Thanks guys


In cell M33 I'm trying to use the same logic where I can get the average of the entire column
=SUBTOTAL(101,M36:M300)
error: #DIV/0!



2.

I would now like to add this across multiple sheets in the same book.
example:

=AVERAGE('John Doe:John Doe5'!C33)

If I do as you suggested:
=SUBTOTAL(101,'John Doe:John Doe5'!C33)
I recevie error: #VALUE!

I'm sure im using your suggestion incorrectly in this case.
 
Upvote 0
thank you both. :) both segestions worked. :cool: now, i'm lookin at the whole book. two part question.

1.

With in the same sheet I'm trying to do the following


Column M: rows 36 thru 300 I'm now using this:
=SUBTOTAL(101,C36:L36)

Works Great Thanks guys


In cell M33 I'm trying to use the same logic where I can get the average of the entire column
=SUBTOTAL(101,M36:M300)
error: #DIV/0!



2.

I would now like to add this across multiple sheets in the same book.
example:

=AVERAGE('John Doe:John Doe5'!C33)

If I do as you suggested:
=SUBTOTAL(101,'John Doe:John Doe5'!C33)
I recevie error: #VALUE!

I'm sure im using your suggestion incorrectly in this case.
You'll get the #DIV/0! error if there are no numbers to average.

You can't use the SUBTOTAL function across multiple sheets.

What you could do is enter a formula like this on each sheet in the same cell (out of the filtered range) and then get the average of that cell.

For example, this formula entered in cell A1 of each sheet:

=SUBTOTAL(9,C33)

Then you can get the average of cell A1 across all the sheets:

=SUM('John Doe:John Doe5'!A1)/INDEX(FREQUENCY('John Doe:John Doe5'!A1,0),2)

Assumes the numbers to average will always be positive numbers.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top