Graph Data smoothing (Excel)

[bluerayz]

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

[Jezza]

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 ?

[bluerayz]

I was looking at the solver tool to do this. have you used this before?

[HansV]

I don't see what Solver could do here.

[HansV]

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.

[bluerayz]

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?

[Jezza]

Patrick

This is similar to what Hans has done manually and what I did with the trendline:

Analysis Toolpak-Exponential smoothing

[HansV]

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.

[HansV]

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.

[bluerayz]

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.

[HansV]

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.

[bluerayz]

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.