Solved: newbie question regarding averaging arrays based on dates

[divingdingo]

so day two of VBA and another problem presents itself! :banghead:

i would like to know how to average a column of data based on what date it is.

what i mean by this is that i have two columns of data. column A is a time stamp: dd/mm/yyyy hh:mm, and column B is a wind speed.

i would like to average the windspeed according to month

i really am not too sure where to start looking for info on how to do this, and would really appreciate someone just pointing me in the right direction to start with, and give me a few suggestions as to what VBA functions i would need to research.

thank you

mark

[unmarkedhelicopter]

You mean average by month/year as I'm preety sure you don't want to find the average windspeed for all Januarys since 1880 (or whatever your data goes back to).
Averages in this case are a bit misleading here as what you mean is an average of windspeeds at the time I took them on the days that I took them for that month. That's not to say that you actually have the average windspeed as you'd need an integrating turbine that logs and resets the values at midnight, thus averaging it over a full 24 hours.
Despite this you'd probably need to use an Array formula or better still a Sumproduct (sum for a given month and divide by the count of reading)
XLD has a site (given in many posts) that will give you a better understanding of how to do this.

Still if you post us some data we can give you something as a starting point.

[divingdingo]

thanks for the pointers, you're right in that i want to average each month independently of year.

the datalogger that i have actually sends me the average windspeed for every 10mins of every day, so it's not just a spot reading every 10mins but an actual average. however i want to build up seasonal trends by having a monthly average.

i just looked up SumProduct and it would suggest that the arrays that it does the calculations on need to be the same size. unfortunaely due to the nature of the calendar and things every month will have a different number of 10min readings, thus will be a different size.

i hope i haven't bitten off more than i can chew with this!

mark

[unmarkedhelicopter]

Yeah I hear what you say, but by definition an average of averages is seldom correct. (Edit: though you at least should be averaging over consistent periods so it should be pretty close)
Post some data and we'll give it a go.
You will probably need a "Month Average Sheet" so we can take the values to get the month to average for.

[divingdingo]

you are correct, there is always going to be an element of error regard this type of data, and i guess i could bring it right back to the calibration of the anemometer and the accuracy of that. the nature of scientific field data hey?!

i've attached a zip file, with an exaple of the data on. i've deleted alot of the rows in each month just so that i can upload it but it gives a clear idea of the data i'm dealing with.

i really appreciate this help. and hopefully i will be able to understand any solution that is found. as i would not only like a solution to this problem but also to learn a little bit more in VBA

thank you

mark

[divingdingo]

opps don't think i worked the attachment feature too well!!

this is the type of data:

[divingdingo]

hang on a sec i'll work it out eventually!!

actually can you give me a clue how to do it. i thought i uploaded an attachement but don't see it on this thread!

[divingdingo]

right, think i've finally been patient enough for the upload to take place.

excuse me!!

mark

[Bob Phillips]

=AVERAGE(IF(MONTH(A2:A2000)=8,B2:B2000))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

BTW you can post xls files directly, we got this changed some time ago, no need to zip unless the file is too big.

[unmarkedhelicopter]

Bob's (above) is for ALL Januarys and you can reconfigure that to cater for years too. Here is the sumproduct version.

(Someone has updated the attachement key but not included the xls icon :) )

(Edit: Ooops ! spelling)

[divingdingo]

thank you both for the time you have both spent on this.

i'm going to digest hard the solutions to see if i understand them. more than likely i'll have a small question regarding the syntax!!
this programing isn't always straight forward !!

small steps hey?!

thanks

mark

[Bob Phillips]

Bob's (above) is for ALL Januarys and you can reconfigure that to cater for years too. Here is the sumproduct version.

(Someone has updated the attachement key but not included the xls icon :) )

(Edit: Ooops ! spelling)

All Augusts actually. I think the OP mentioned that it was across years, but to cater for a specific year

=AVERAGE(IF((MONTH(A2:A2000)=8)*(YEAR(A2:A2000=2007),B2:B2000))

still an array formula

[unmarkedhelicopter]

All Augusts actually. I think the OP mentioned that it was across years, but to cater for a specific yearNo he said :-
you're right in that i want to average each month independently of year.and when I said :-
Bob's (above) is for ALL Januarys and you can reconfigure that to cater for years too.That was just to tie in with what I'd said in the second post :-:-
You mean average by month/year as I'm preety sure you don't want to find the average windspeed for all Januarys since 1880 (or whatever your data goes back to). ... but although I did see the 8, I didn't include the """ quote marks to put it in context, so I'll accept that the cock up was mine. :)

[rory]

Personally I would use a pivot table for this, as in the attached example.

[divingdingo]

after a long weekend and finally getting back to work and looking at all the solutions that have been posted i've finally come to mark the thread solved.

although i've only just read rory's post regarding 'pivot tables' and now feel i might have something else to investigate.

from the help of the previos posts i have something that works and is alot quicker than previous.

a huge thankyou

mark