Average without Hidden Cells

[Goody]

I need to average values in a row, where some columns may be hidden. I do not
want the hidden values to be used in the Average calculation. The hidden
column may not always be the same. Can this be done without itemizing the
cells in the Average formula?

 

[Luke M]

If you have XL 2003 or later:
=SUBTOTAL(101,A2:E2)

 

[Mike H]

Hi,

Annoyingly the SUBTOTAL function does this on columns only and not rows so
we have to improvise.

say your date are in A1 - J1
put this in A2 and drag right to J2

=CELL("width",A1)>0
You can now hide this row if you want

Now if you have E2007 use the formula below to average visible rows only
=AVERAGEIF(A2:J2,TRUE,A1:J1)

If you don't have e2007 then use
=SUMIF(A2:J2,TRUE,A1:J1)/COUNTIF(A2:J2,TRUE)

One note is that hiding/unhiding rows/cols doesn't cause Excel to calculate
so if you hide/unhide then tap F9 to get the result to update
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Luke,

That only works on columns.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Luke M]

Thanks for the correction. Since the basic functionality worked across
columns, and made the bad assumption that it would handle the hiding of
columns.