Formula to update and show Avg based on lines displayed

[bradjedis]

Greetings,

I am looking to have a formula to show the avg of a column of data, and to have the displayed avg change based upon any filtering applied to other columns.

Target cell for Formula: M4
beginning cell of data in the column M9

basic filter will be against column E, with a secondary being some other random col.

Thanks,
Brad

[HansV]

In M4:

=SUBTOTAL(1,M9:M100)

Adjust the range if needed (it doesn't matter if it contains empty cells at the end; they will be ignored)

[bradjedis]

Hans,

good deal. Is there a way to get around having to refresh the calculations? After filtering, I have to press F9...

[HansV]

Make sure that Calculation Options on the Formulas tab of the ribbon is set to Automatic. Excel should automatically update the result of SUBTOTAL as you filter.

[bradjedis]

wow that was a total brain fart. Cannot believe I forgot that.


Thanks