VBA macro to average

[randym44]

My VBA knowledge is fairly basic so I'm looking more for an answer
than help with developing the code.

I typically have data files with 1 data point per weekday that will
sometimes be 15 years long. I would like to have a macro that will
give me a series of monthly averages of the daily data for each month.
Obviously I can do it manually by entering the average function for
each month's daily data range, but going thru 15 years of daily data,
with a different number of weekdays in each month, to calculate a
monthly average for each is a pain. Any suggestions or help would be
greatly appreciated.

Thanks

 

[Dave Peterson]

I'd consider using a pivottable.

You can group by month and year and show averages (or sums or max or...)

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

===========
Depending on how your data is laid out, you could use a bunch of formulas:

=average(if(text(a2:a1000,"yyyymm")="200710",b2:b1000))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And you can't use the whole column until xl2007.

=========
Or you could add a helper column to your data:
=text(a2,"yyyymm")
(and drag down)
Then sort your data by this the helper column.
Then apply data|subtotals, choose average for the field you want.
(xl2003 menu system)

 

[randym44]

I'd consider using a pivottable.

You can group by month and year and show averages (or sums or max or...)

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):http://office.microsoft.com/downloa....com/assistance/2002/articles/xlconPT101.aspx

===========
Depending on how your data is laid out, you could use a bunch of formulas:

=average(if(text(a2:a1000,"yyyymm")="200710",b2:b1000))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And you can't use the whole column until xl2007.

=========
Or you could add a helper column to your data:
=text(a2,"yyyymm")
(and drag down)
Then sort your data by this the helper column.
Then apply data|subtotals, choose average for the field you want.
(xl2003 menu system)

Thanks Dave. I like the use of data|subtotals but I've never found a
way to seperately copy/extract just the resultant averages in this
case. If I have a set of daily data that is 1 year in length, so
approx 240 daily workday data points, I can easily get subtotal to do
the averages for me. Once I have the subtotals and I collapse the data
to only show the subtotals, is there any way to only copy that set of
data out? If you highlight and copy and paste, even though the full
set of data isn't showing in the collapsed subtotals, they do paste
when you move the data.

The other array formula does produce exactly what I'm wanting in
finding the data and calculating the average, but I'm trying to avoid
a series of different formulas as I will always require the averages
over the entire dataset which can sometimes be 20 years long.

I should probably brush up on pivot tables too since I know they can
be very useful, but probably under-utilized by most.

 

[Dave Peterson]

After you hide the details, you can select the range to copy.

But then do
Edit|Goto|Special|visible cells
Then do the copy|paste special|values

 

[randym44]

After you hide the details, you can select the range to copy.

But then do
Edit|Goto|Special|visible cells
Then do the copy|paste special|values

(e-mail address removed) wrote:

<<snipped>>








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Ahh. That will do it. Thanks a ton Dave.