We help IT Professionals succeed at work.

Getting an column Average, with AutoFilters applied.

What would a formula look like, if I try to average a datatable that has 2 or 3 autofilters applied to it?  Whenever I apply the filters and put in =AVERAGE(range1) it includes the hidden cells in the result?  I select the header and do a Ctrl+down, then offset activecell (1,0).Select to get to bottom of column.  Then I want to add the formula.  Any idea how this formula should look or be so it does not include those filtered out cells?  Please advise and thanks. -R-
Comment
Watch Question

"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
=SUBTOTAL(1, range1)

The "1" represents AVERAGE. In the formula bar if you delete the "1" a list of other functions will appear. "101" (in this case) would also ignore manually hidden rows.

Author

Commented:
Thanks this worked... can I ask another minor question?

I use:
Range("D1").Activate
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select  'one down

To get to the bottom of a filtered set, however if there are filtered out rows, it does not give me the right cell.  Is there a way to do an xlup to get the cell under the filtered dataset?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm not sure what you are asking. If you had 1, 2, 3, ... 10 in column D1, Q2, D3..., D10, and only 3, 4 , and 5 were showing, which row would you want to select?

If you want to select the last visible cell then this works.

Cells(Range("D1048576").End(xlUp).Row, "D").Activate

Author

Commented:
The filter shows 3,4,5.  I would like row 11.  6,7,8,9, and 10 are hidden.  The code I am using highlights/goes to row 6.  I was wondering if there is an easier way to get to row 11 using xlup instead of xldown?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This uses the row portion of autofilter range address.

Range(Split(ActiveSheet.AutoFilter.Range.Address, ":")(1)).Offset(1, 0).Activate

Author

Commented:
Yup that's it.... now to get it to the proper column.....   can I tell that line of code to Activate a specific column? like column D?
Thanks for helping with the extended question...   I know that you did not have too and probably should have been its own separate question. .  -R-
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
It's not too late to do that:)
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
That's OK, never mind.

Range("D" & ActiveSheet.AutoFilter.Range.Rows.Count + 1).Activate

Author

Commented:
THANKS!!

Author

Commented:
THANKS!!
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017