+ Reply to Thread
Results 1 to 5 of 5

Smoothing Data

  1. #1
    Registered User
    Join Date
    06-10-2016
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    11

    Smoothing Data

    I am trying to smooth my data. I want it to look like the data in the Normal Highs and lows as in the Word document. The data I am trying to smooth is in the Excel document in the Average high and lows column.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    9,470

    Re: Smoothing Data

    Do you know what kind of smoothing algorithm you want to use?

    The normal highs/lows in the Word document are very smooth -- making me think that they are the result of some kind of regression algorithm -- maybe even based on data for the entire year and not just January. I don't know if your goal is to exactly replicate this, or just come up with any kind of algorithm that gives smooth values like this.

    If your goal is to exactly replicate this, you probably need to research how the source for the word document obtained those numbers so you can understand their algorithm and implement it here.

    If your goal is to come with any suitable algorithm, I would probably start with a basic, empirical polynomial (quadratic) model. This can be accomplished fairly easy in Excel using the LINEST() function to generate the polynomial's coefficients. From those coefficients, it should be straightforward to generate the smoothed data.

    LINEST() help file:https://support.office.com/en-us/art...a-fa7abf772b6d Pay particular attention to the example for a cubic polynomial near the end of the Remarks section -- just before the first main example.

    It's not a definitive answer, but it should give you something to start with.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-10-2016
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    11

    Re: Smoothing Data

    Can you show me in my data? I am not very good at this. I am trying to get something like my example in the word document.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    9,470

    Re: Smoothing Data

    I can't upload a file at the moment. Does a linear/polynomial regression seem like what you want, or some other regression time? Was there something specific in the help file that you were unable to understand? Did you try any of the examples to get a feel for how the LINEST() function works?

    Try this:

    1) Select BN42:BP42.
    2) Enter LINEST() function as described in help file =LINEST(BN5:BN35,A5:A35^COLUMN(A:B)) Remember that LINEST() is an array function, so it must be confirmed with ctrl-shift-enter.
    3) In BM5, enter =$BP$42+$BO$42*A5+$BN$42*A5^2. Copy down.
    4) Evaluate the results in BM5:BM35 to see if those are the results you want.

  5. #5
    Registered User
    Join Date
    04-14-2018
    Location
    FL, Miami
    MS-Off Ver
    2016
    Posts
    11

    Re: Smoothing Data

    I thinks it is very helpful info, thanks you pats

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1