+ Reply to Thread
Results 1 to 10 of 10

Find exact formula of a curve and fix the bumps in the curve

  1. #1
    Registered User
    Join Date
    12-01-2014
    Location
    Trevose, PA
    MS-Off Ver
    2010
    Posts
    9

    Find exact formula of a curve and fix the bumps in the curve

    sampledata.xlsxI created a chart from my data points, and my excel "curve" is flat at the beginning and bumpy at the end. I want to be able to adjust the curve so that the beginning is not flat and the end is not bumpy. I am new to statistics, and looking for a way to adjust the data. Any help would be appreciated. Thanks. Also would like to get the exact formula of my bumpy curve not of the trendline. Thanks.

    I attached a sample file (not my exact data, but a similar situation. So how do I find the exact formula of this curve and how to fix it to eliminate the flat and bumpy. Thanks.
    Last edited by twobox; 01-16-2015 at 11:26 AM. Reason: adding attchment

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Find exact formula of a curve and fix the bumps in the curve

    To start, we would need a sample workbook of the data. Click on Go Advanced --> Manage Attachments to upload.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    12-01-2014
    Location
    Trevose, PA
    MS-Off Ver
    2010
    Posts
    9

    Re: Find exact formula of a curve and fix the bumps in the curve

    Thanks Attached sample file.

  4. #4
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Find exact formula of a curve and fix the bumps in the curve

    I have smoothed the data using the LOESS smoothing technique. This is a UDF created in the 22 Worksheet class module.

    The main reason the trail end of your data is returning non-uniform results is because of the discourse in the y-axis starting at cell B29 (going from 23,24,25 to 30). The LOESS function (column D) smoothes the data to a 17 moving point regression (http://peltiertech.com/loess-smoothing-in-excel/ for more information), and cells B31:D33 show a forecast of what that data would look like if the trend were to keep going.

    The equation derived from the trendline shown at the top of the chart is based on the LOESS values, not the actual Cost data.

    Hope this helps!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-01-2014
    Location
    Trevose, PA
    MS-Off Ver
    2010
    Posts
    9

    Re: Find exact formula of a curve and fix the bumps in the curve

    Thanks That is helpful. Can you tell me how to find the exact formula of the original line?

  6. #6
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Find exact formula of a curve and fix the bumps in the curve

    Unless I'm mistaken, you already had the exact formula for the original line based on the trendline you had for your raw data. You had asked for a formula to fix the "curves and bumps", and that is what LOESS smoothing does.

  7. #7
    Registered User
    Join Date
    12-01-2014
    Location
    Trevose, PA
    MS-Off Ver
    2010
    Posts
    9

    Re: Find exact formula of a curve and fix the bumps in the curve

    Isnt the formula for the trendline not the original data, just a line of best fit. I want the exact formula of the line.

  8. #8
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Find exact formula of a curve and fix the bumps in the curve

    Is the data empirical or theoretical? It appears to be empirical, in which the trendline formula is the derived theoretical formula for the raw data based on linear regression.

  9. #9
    Registered User
    Join Date
    12-01-2014
    Location
    Trevose, PA
    MS-Off Ver
    2010
    Posts
    9

    Re: Find exact formula of a curve and fix the bumps in the curve

    It is empirical, so you are saying the trendline is the exact formula for the line? Thanks again.

  10. #10
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Find exact formula of a curve and fix the bumps in the curve

    If you are unaware between the distinction between empirical and theoretical results, I suggest you do some quick googling and read up on it. It is imperative you know the difference, especially when working with data. The "theoretical" results are something that has been derived (eg. F=ma, V=IR, etc.). These are results in "ideal" situations, conditions which are almost never met. It is then necessary to take your "empirical" data, and see how much deviation there is from your "theoretical" calculations.

    I say this because no empirical data should return the "exact formula" of the theoretical results due to the imperfect nature of our world.

    Long story short, the trendline represents the closest approximation as to the behavior of the data you put into the chart. However, you must note that the trendline is only as good as the data - garbage data in, garbage analysis out.

    Hope this helps!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to make a bell curve/statistical curve????
    By pittstonacl in forum Excel General
    Replies: 1
    Last Post: 08-04-2014, 10:49 AM
  2. Need to derive curve from values, then apply curve to another data set
    By Telperion in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-13-2014, 09:57 PM
  3. Graph a curve, then enter data to generate new similar curve
    By denphi03 in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 11-03-2013, 05:33 PM
  4. Normal distribution curve / Bell curve
    By LAG1 in forum Excel General
    Replies: 0
    Last Post: 05-24-2012, 07:20 AM
  5. curve fitting a charging capacitor type curve
    By mcgradys in forum Excel General
    Replies: 4
    Last Post: 11-15-2005, 08:50 AM

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