How to weight averages for unevenly distributed replicates

[cgrubb]

In case the title of this thread didn't make sense (it barely does to me), here's a more detailed description:

I am trying to calculate the background levels of a number of pollutants in the sea. My data comes from several measuring stations, that take samples at uneven intervals. I have data from 2007-2014, but not all stations have data from each year.

I would like to calculate a weighted average of each pollutant at each station, by somehow assigning each data point a weight, based on its age. So if one stations has measured pollutant A in 2014, 2013 and 2012, a total of 1+2+3=6 years ago, the average could be calculated like:

mean = [2014]*3/6 + [2013]*2/6 + [2012]*1/6

Or if the measurements were performed in 2014, 2013 and 2011 and 2009 (a total of 14 years ago) it could be:

mean = [2014]*7/14 + [2013]*4/14 + [2011]*2/14 + [2009]*1/14

Does that make it clearer? I don't know, I guess this need some sort of VBA, but I've only ever implemented code that someone else wrote...

Anyway here's an example: example for excelforum.xlsx

[shg]

What's the rationale for that? If the the most current measurements are accurate, what does a weighted average tell you?

[JeteMc]

In looking through the data by applying the filter to column A I can not find where any one station is listed as having monitored for more than one day. Do you just want an average of each pollutant weighted by the year the measurement was taken?