How to ignore hidden columns in an "Average" formula?

[DotK]

If an "average" formula includes multiple columns, and those columns change
each month by hiding old months (at end) and adding new months (at beginning)
what can be done, by macro or in formula) to ignore the hidden columns?

 

[Ron de Bruin]

Try this if you can use 101 in your Excel version

=SUBTOTAL(101,A1:C1)

 

[KC hotmail com>]

Use SUBTOTAL(101,YourRange)

 

[Brad]

Ron,

I thought "=subtotal(101,range)" only was affected "rows" were hidden - not
the columns?

 

[Ron de Bruin]

Hi Brad

No, also columns

Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to
use in calculating subtotals within a list.

 

[Dave Peterson]

In xl2003, hidden columns are not ignored.

Ron, did that change in xl2007 or xl2010?

(or maybe celebrating the New Year too early???)

 

[Brad]

From the 2007 Excel help

The SUBTOTAL function is designed for columns of data, or vertical ranges.
It is not designed for rows of data, or horizontal ranges. For example, when
you subtotal a horizontal range using a function_num of 101 or greater, such
as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But,
hiding a row in a subtotal of a vertical range does affect the subtotal.

How are you getting the 101 to not count the hidden columns?

 

[Ron de Bruin]

Hi Dave and Brad
Yes that it is

I would swear that I test it in 2007 yesterday and it worked but I must admit
that we drink beer at work before I test it <vbg>

Everybody a Happy New-year

 

[Dave Peterson]

Happy New Year to you, too, Ron!

Hi Dave and Brad

Yes that it is

I would swear that I test it in 2007 yesterday and it worked but I must admit
that we drink beer at work before I test it <vbg>

Everybody a Happy New-year