Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    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
    Attached Files Attached Files

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    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

  4. #3
    2 Star Lounger
    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?

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Graph Data smoothing (Excel)

    I don't see what Solver could do here.

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 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.
    Attached Files Attached Files

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

  8. #7
    Platinum Lounger
    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 Toolpak-Exponential smoothing
    Jerry

  9. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 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.
    Attached Files Attached Files

  10. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 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.

  11. #10
    2 Star Lounger
    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.
    Attached Files Attached Files

  12. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 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.
    Attached Files Attached Files

  13. #12
    2 Star Lounger
    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.
    Attached Files Attached Files

Posting Permissions

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