graph smoothing
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. |
All times are GMT +1. The time now is 12:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com