Formula based on visible rows only

jamie.kenyon wrote:

I'd like to filter based on columns B and C (market conditions), and have the average % changes update - is this possible?

Yes, have a look at the SUBTOTAL function.

SUBTOTAL - Apple Support

"You can skip hidden rows by adding 10 if function-num is a single digit and 1 if function-num is two-digits."

Following is a simple example with an implausibly good period at the end, perhaps an outlier that you want to exclude, but really just a made up number to show the difference in results after you filter.

Before filtering both formulas yield the same result:

=SUBTOTAL(106,Table 1::M)−1

After applying a filter to column B to exclude market condition A:

Recommend basing your formula on the entire column Numbers style (rather than the Excel-like range within a column that you have) and using Quick Filter on the entire column (in the dropdown by the column letter). You can set more complex filters if needed in the Organize > Filter tab in the pane at the right.

SG