Monthly average calculations

[Guest]

Hi

I have a sheet containing a full years data: columns conatining parameters
and rows containing recordsets per day of the year.
I have a button that hides rows per a month selection in a combo box. I
want to calculate averages for all the columns for a specific month. This
must be displayed in the last row - this actually being the empty row below
the 31 Dec 2005. This row is vissible at the bottom of each displayed month.

When I just put a function in there it only calcualtes the average for a
fixed number of rows. How do I change it that it calculates the average
dynamicaly as per the month selected.

This is to enable a monthly report to be printed out that shows average at
the bottom of each column.

thanks
Francois

 

[Arvi Laanemets]

Hi

For example for column C with data in range C2:C367 ( a spare row for leap
year)
=SUBTOTAL(1;$C2:$C367)

 

[Bob Phillips]

Hi Francois,

Assuming the data to be summed is in A1:An, and the date for the month to
test against is in B1, try this

=SUM(INDIRECT("A"&DATE(YEAR(B1),MONTH(B1),1)-DATE(YEAR(B1),1,0)&":A"&DATE(YE
AR(B1),MONTH(B1)+1,1)-DATE(YEAR(B1),1,1)))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Arvi Laanemets]

Hi

A notation: I'm not sure how SUBTOTALS work when you hide rows through
code - depends on how you do the hidding I think. The formula works for
sure, when Autofilter is used to hide rows.


Arvi Laanemets