macro that calculates average

  • Thread starter Thread starter jaguarr
  • Start date Start date
J

jaguarr

I have Excel data similar to the structure below

columnA Column B Column C Column D
month day time temperature
1 1 100 32
1 1 130 33
1 2 100 29
1 2 130 35
1 2 200 33
1 2 230 36

I would like to calculate the daily average temperatures. It would b
easy if every month have the exact same number of days, but it doesn't
and I have many years and many months to calculate, so I think macro i
the easiest and fastest way to do this.

Could someone write a basic macro that will calculate the daily averag
temperature? I assume one 'if statement' would be to check if the da
(column B) is the same.

thanks for any input
 
This is much more easily accomplished with a pivot table. Take your Month /
Day / Year / Time columns and create an actual single Date and Time column
(you probably don't need the time data and it could get in the way if you end
up with more than 8,000 unique values for Date and Time so you probably don't
want it). You can then pivot that data grouping on the Date (into months,
quarters, years) and aggregating with the average function. This is a little
bit advanced in terms of pivot tables so let me know if you need any help. If
it was me I would create a sheet that looked like this...
month day Date time temperature
1 1 1/1/2005 100 32
1 1 1/1/2005 130 33
1 2 1/2/2005 100 29
1 2 1/2/2005 130 35
1 2 1/2/2005 200 33
1 2 1/2/2005 230 36
Click to expand...

Which will pivot very nicely...
 
Back
Top