how do you smooth a graph which has multiple spikes in it?


--
maksud
------------------------------------------------------------------------
maksud's Profile: http://www.excelforum.com/member.php...o&userid=26083
View this thread: http://www.excelforum.com/showthread...hreadid=394139

Try adding a trendline - rolling average.
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"maksud" wrote in
message ...

how do you smooth a graph which has multiple spikes in it?


--
maksud
------------------------------------------------------------------------
maksud's Profile:
http://www.excelforum.com/member.php...o&userid=26083
View this thread: http://www.excelforum.com/showthread...hreadid=394139

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 NFilters.
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.


Function FiltRange(Data As Range, DataPoint As Range, NFilter As Integer, _
Optional Derivative) As Double
Dim M As Integer, R As Integer, C As Integer, S As Double, _
R1 As Integer, R2 As Integer, C1 As Integer, C2 As Integer, _
I As Integer, J As Integer, K As Integer, N As Integer, _
Deriv As Boolean, DataInColumn As Boolean
Static ArchN As Integer, ArchDeriv As Boolean, _
AA As Variant, SA As Double
If Not IsMissing(Derivation) Then If Derivation 0 Then Deriv = True
M = Data.Count
R1 = Data(1).Row
R2 = Data(M).Row
C1 = Data(1).Column
C2 = Data(M).Column
R = DataPoint.Row
C = DataPoint.Column
N = NFilter
DataInColumn = C1 = C2
If DataInColumn Then 'detection of the arrangement of Data
If R - R1 < N Then N = R - R1
If R2 - R < N Then N = R2 - R
Else
If C - C1 < N Then N = C - C1
If C2 - C < N Then N = C2 - C
End If
If N = 0 Then
If Not Deriv Then
FiltRange = DataPoint.Value 'value identical with the data
'derivative at both ends:
ElseIf DataInColumn And R = R1 Or Not DataInColumn And C = C1 Then
FiltRange = Data(2).Value - Data(1).Value
ElseIf DataInColumn And R = R2 Or Not DataInColumn And C = C2 Then
FiltRange = Data(M).Value - Data(M - 1).Value
Else 'derivative inside
If DataInColumn Then K = R - R1 + 1 Else K = C - C1 + 1
FiltRange = (Data(K + 1).Value - Data(K - 1).Value) / 2
End If
Exit Function
End If
If N 5 Then N = 5 'restriction to N<=5
If N < ArchN Or Deriv = ArchDeriv Then 'if different from previous N
Select Case N
Case 1
If Not Deriv Then AA = Array(1, 2, 1): SA = 4 _
Else AA = Array(-1, 0, 1): SA = 2
Case 2
If Not Deriv Then AA = Array(-3, 12, 17, 12, -3): SA = 35 _
Else AA = Array(-2, -1, 0, 1, 2): SA = 10
Case 3
If Not Deriv Then AA = Array(-2, 3, 6, 7, 6, 3, -2): SA = 21 _
Else AA = Array(-3, -2, -1, 0, 1, 2, 3): SA = 28
Case 4
If Not Deriv Then AA = Array(-21, 14, 39, 54, 59, 54, 39, 14, -21): SA =
231 _
Else AA = Array(-4, -3, -2, -1, 0, 1, 2, 3, 4): SA = 60
Case 5
If Not Deriv Then AA = Array(-36, 9, 44, 69, 84, 89, 84, 69, 44, 9,
-36): SA = 429 _
Else AA = Array(-5, -4, -3, -2, -1, 0, 1, 2, 3, 4, 5): SA = 120
End Select
ArchN = N
ArchDeriv = Deriv
End If
S = 0
J = LBound(AA) 'index base
For I = -N To N
If DataInColumn Then K = R - R1 + I + 1 Else K = C - C1 + I + 1
S = S + AA(J) * Data(K).Value
J = J + 1
Next I
FiltRange = S / SA
End Function

--
Best wishes
Petr Bezucha


Bernard Liengme pÃ*Å¡e:

Try adding a trendline - rolling average.
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"maksud" wrote in
message ...

how do you smooth a graph which has multiple spikes in it?


--
maksud
------------------------------------------------------------------------
maksud's Profile:
http://www.excelforum.com/member.php...o&userid=26083
View this thread: http://www.excelforum.com/showthread...hreadid=394139

If this is a line or xy scatter chart you can double-click the plotted
series and from the Patterns tab select the 'Smoothed line' checkbox.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

how do you smooth a graph which has multiple spikes in it?


--
maksud
------------------------------------------------------------------------
maksud's Profile: http://www.excelforum.com/member.php...o&userid=26083
View this thread: http://www.excelforum.com/showthread...hreadid=394139

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.