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