Calculating deviation from mean and % change for varying years

[smurray444]

Dear all,

I have calculated rates of evaporation for a large number of global sites for the year 2050. I want to calculate how far this new value deviates from the mean of the preceeding years' evaporation values, and the percentage change.

I would normally be able to do this myself, but the problem I have is that for each global site, the number of years of 'previous' data (to which the new value should be compared) varies. e.g. For one site this may run from say, 1964-1986, whereas the proceeding one may be 1958-1990, for example.

I also require the deviation value to be placed in the same row as the new value for year 2050 appears (year is in column E) - I can create a new column for this (would be column Q). Similarly, the percentage change should also be on the same row as this year (would go into a new column also, R).

A couple of other details: the column which denotes a change of site is column A (ie. when this value changes, it is a new site); evaporation values are in column P.

I hope someone is able to help me with this!
Please do not hesistate to get back to me if you need clarification.

Thanks,
Steve

[bob777]

what you are doing, i believe, is calculating a rolling mean and comparing this years result with last years


y1 y2 y3 y4 y5
3 7 6 5 7


the average of the first 4 is 5.25

for all 5 it is 5.6


Is this what you want to do, the formula is simple.

Bob