Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Monthly average calculations
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Monthly average calculations
Hi
For example for column C with data in range C2:C367 ( a spare row for leap year) =SUBTOTAL(1;$C2:$C367) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Francois" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Monthly average calculations
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Monthly average calculations
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) "Francois" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rolling Monthly Average | Excel Discussion (Misc queries) | |||
Rolling 12 Monthly Average | Excel Discussion (Misc queries) | |||
Rolling Monthly Average | Excel Worksheet Functions | |||
Monthly Average in a PivotTable | Excel Discussion (Misc queries) | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) |