# Thread: Graph Data smoothing (Excel)

1. ## 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

2. ## 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 ?

3. ## Re: Graph Data smoothing (Excel)

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

4. ## Re: Graph Data smoothing (Excel)

I don't see what Solver could do here.

5. ## 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.

6. ## 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?

7. ## Re: Graph Data smoothing (Excel)

Patrick

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

Analysis Toolpak-Exponential smoothing

8. ## 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.

9. ## 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.

10. ## 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.

11. ## 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.

12. ## 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•