Hi Petr,

Although we may be getting away from what the original poster intended, I
quite agree with you and much prefer to use your filter method to smooth
data.

For monthly time series, you can also use a 13 point henderson filter which
you can get at
http://edferrero.m6.net/vba.shtml.

Ed Ferrero
http://edferrero.m6.net/



Hallo friends,
Excel rolling average is a slightly unfortunate issue, acceptable only for
rough demonstration of the trend. If you observe it more closely, you find
that with increasing optional number of averaged points the curve is
shifted
more and more to the right from its correct position. Especially if you
have
peaks on the curve, it looks awful. The next disadvantage may be you
cannot
obtain numeric values for any further treatment. Beside the more correct
rolling averages you may find several mathematical filters in the
literature
that are recommendable. A UDF suitable for equidistant data, i.e. from
usual
measurements, you will find below. It is a little sumptuous, for it allows
even for the treatment of worksheet data arranged even in a row, at the
beginning and the end of the series, and calculates optionally the
derivatives (Derivative0). Naturally it can be largely simplified. Data
include the whole range of data, while the Function formula should be
linked
(in one row) with the proper DataPoint from the Data range. NFilter
corresponds in a practical sense to a number of averaged points in rolling
average. You will normally copy the function formula, with anchored Data
and
NFilter arguments, along with the original data, and make plot from
original
points together with the curve from UDFs. The main advantage of this UDF
is
selecting NFilter from a single cell, so that you see the impact on the
plot
immediately, or you can even plot several curves with different NFilter's.
You can so resolve for the proper parameter more sensibly, which, with
many
peaks that should not be destroyed and low number of points, is often a
matter of taste.