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

  • Thread starter Thread starter DotK
  • Start date Start date
D

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,

I thought "=subtotal(101,range)" only was affected "rows" were hidden - not
the columns?
 
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.
 
In xl2003, hidden columns are not ignored.

Ron, did that change in xl2007 or xl2010?

(or maybe celebrating the New Year too early???)
 
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?
 
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
 
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
Click to expand...
 
Back
Top