Results 1 to 12 of 12
Thread: Graph Data smoothing (Excel)

20070528, 20:15 #1
 Join Date
 May 2005
 Location
 Vancouver, Br. Columbia
 Posts
 223
 Thanks
 0
 Thanked 0 Times in 0 Posts
Graph Data smoothing (Excel)
I have attached a spreadsheet which I have made a graph in.
I was wondering if excel has some data smoothing functions or if anyone knows I can filter my data over a time interval (15 sec) and grab the average of the data in the time from to smoothen the graph line. on the attached I have drawn a line that I would like to see, but wondering how I can manipulate the data to achive the same line in a chart and not draw it?
Thanks

20070528, 20:37 #2
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Graph Data smoothing (Excel)
Hi Patrick
Could you just add a trendline. I added a polynomial or order 6....is that what you want or is it specifically an averaging ?Jerry

20070528, 20:52 #3
 Join Date
 May 2005
 Location
 Vancouver, Br. Columbia
 Posts
 223
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph Data smoothing (Excel)
I was looking at the solver tool to do this. have you used this before?

20070528, 21:11 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 20 Times in 20 Posts
Re: Graph Data smoothing (Excel)
I don't see what Solver could do here.

20070528, 21:23 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 20 Times in 20 Posts
Re: Graph Data smoothing (Excel)
Try the attached version. It uses a rolling average over a number of entries. The number (which must be odd) is specified in P1. A small number such as 3 or 5 leaves a lot of "noise"; a large number such as 37 removes almost all variation. You can experiment with different values to see which one suits you best.

20070528, 21:25 #6
 Join Date
 May 2005
 Location
 Vancouver, Br. Columbia
 Posts
 223
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph Data smoothing (Excel)
after remebering a conversation with someone they mentioned that the best way to smoothen a line is to use Analysis toolpack which would build a formula through regression analysis of the data. The formula would then interpolate a theorectical line for me.
Do you know what he was talking about? if so have you done this before?

20070528, 21:33 #7
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Graph Data smoothing (Excel)
Patrick
This is similar to what Hans has done manually and what I did with the trendline:
Analysis ToolpakExponential smoothingJerry

20070528, 22:04 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 20 Times in 20 Posts
Re: Graph Data smoothing (Excel)
The attached version demonstrates both method for smoothing. Cell Q2 contains the smoothing factor for exponential smoothing.
Once again, experiment with the parameters to see which method and which parameter suits your purposes.

20070529, 16:14 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 20 Times in 20 Posts
Re: Graph Data smoothing (Excel)
Your chart is hard to read now, with only three series with slightly more than 100 points each. It will become extremely crowded and virtually unreadable with
a) 3 data series with thousands of points each (I assume that the data will eventually continue to row 5000)
[img]/forums/images/smilies/cool.gif[/img] 3 smoothed series
c) error bars
I think you should reconsider what you really need.

20070529, 17:38 #10
 Join Date
 May 2005
 Location
 Vancouver, Br. Columbia
 Posts
 223
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph Data smoothing (Excel)
yes 3 data series, so I made it one now using the max values of the three series.
I then plotted the series i made, and drew a polynomial trendline.
I then used the polynomial equation used for the trendline L1:R1 constants and Col O17:O125 has the new data
For some reason I cannot get the polynomial equation to work. I think its because of the time.
any suggestions
also the series will not be 5000 that was just use to grab the series.

20070529, 17:53 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 20 Times in 20 Posts
Re: Graph Data smoothing (Excel)
I'm feeling completely lost.
You replaced the zip file in <post:=652,673>post 652,673</post:> with a completely different workbook that doesn't have a discernible relationship with the original one.
I don't understand what you're trying to do in the workbook in the zip file you attached to your latest reply.
For what it's worth, I have attached a zip file with a chart showing a smoothed line and error bars for just one of your three original data series.

20070529, 18:36 #12
 Join Date
 May 2005
 Location
 Vancouver, Br. Columbia
 Posts
 223
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph Data smoothing (Excel)
Thank very much help Hans and Jezza. I am although trying to figure out a way to have a smoother line like a trend line but a little more accurate at showing slop changes gradual and filtering out large change in a short time frame (like the one I drew in my first post).
Basically what I am trying to do is graph three lines as one and show error bars for any difference. In the first post I attach just 1 lines data of . I am not sure if this make a difference but I have attached all my data and a graph of the actual.