Averaging

Hello,

I have a spreadsheet that I use formulas like this one: =SUBTOTAL(9,(C11:C110))

I am wondering if it is possible to make a self adjusting formula for averages. I have tried =AVERAGE(9,(C11:C110)) but it gives me an error. Is it possible to average the data of only rows that are showing if I hide some rows? Thanks for your help!

Bonni

Originally Posted by SMABonni

Hello,

I have a spreadsheet that I use formulas like this one: =SUBTOTAL(9,(C11:C110))

I am wondering if it is possible to make a self adjusting formula for averages. I have tried =AVERAGE(9,(C11:C110)) but it gives me an error. Is it possible to average the data of only rows that are showing if I hide some rows? Thanks for your help!

Bonni

Try...

=SUBTOTAL(1,(C11:C110))

That worked great! Thank you!

How are you hiding the rows? The formula given ignores rows that are not shown due to filtering, it won't exclude values in rows hidden using Hide command. If you want to exclude both hidden and filtered out rows you can use

=SUBTOTAL(101,C11,C110)