Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro to average
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro to average
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/assistan...lconPT101.aspx =========== Depending on how your data is laid out, you could use a bunch of formulas: =average(if(text(a2:a1000,"yyyymm")="200710",b2:b1 000)) 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) wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro to average
On Oct 11, 4:26 pm, Dave Peterson wrote:
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/download...lconPT101.aspx =========== Depending on how your data is laid out, you could use a bunch of formulas: =average(if(text(a2:a1000,"yyyymm")="200710",b2:b1 000)) 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) wrote: 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 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro to average
On Oct 12, 11:42 am, Dave Peterson wrote:
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 wrote: <<snipped 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- Hide quoted text - - Show quoted text - Ahh. That will do it. Thanks a ton Dave. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort, select and average macro help | Excel Discussion (Misc queries) | |||
macro that calculates average | Excel Programming | |||
Average macro/program | Excel Programming | |||
Average Macro | Excel Discussion (Misc queries) | |||
Average If Macro | Excel Programming |