+ Reply to Thread
Results 1 to 11 of 11

How do I create intervals from a best fit curve?

  1. #1
    Registered User
    Join Date
    03-02-2020
    Location
    UK
    MS-Off Ver
    Microsoft 2019
    Posts
    7

    Exclamation How do I create intervals from a best fit curve?

    Hello people,

    The main purpose of my work is to compare few graphs from a repeated experiment and get an average value of them. The raw data is not always ascending(or descending), so I had to create a best fit line to make the data looks like it is ascending only. The graphs however do not have the same starting point. So there are two questions I would like to ask:

    How to you set intervals (I am not sure if I am using the correct term) on the x-axis which can be referred to the best fit line?

    And how do you find the corresponding value on y-axis from the best fit line based on the intervals setting on x-axis?

    Therefore, I can compare the data using the best fit curves with desired intervals.

    Cheers

    DK11

    Edit:
    Graph sample.png

    I would like to get the values on y-axis with a 0.01 interval on the x-axis, based on the curve fit line. How could I use the data from the best fit line?
    Last edited by DK11; 03-02-2020 at 10:40 AM.

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

    Re: How do I create intervals from a best fit curve?

    Difficult to make specific suggestions from just a picture of a chart. Can you upload a sample file (instructions in yellow banner) that shows both the spreadsheet and the chart you are working with?

    One thing I would point out -- charts don't really do much to analyze data. If you want to do something that is not readily available in the chart options, you usually need to do whatever it is you want to do in the spreadsheet. For a piecewise regression (I think that is what you are asking for), this usually means that you need to separate/filter/identify the segments of interest in the spreadsheet and maybe perform the regression in the spreadsheet rather than the chart.

    If you can help us understand exactly what is going on in your spreadsheet and exactly what you want to do, then we should be able to help you figure out how to program that into the spreadsheet.
    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
    03-02-2020
    Location
    UK
    MS-Off Ver
    Microsoft 2019
    Posts
    7

    Re: How do I create intervals from a best fit curve?

    Hi MrShorty

    Thanks for your reply.

    There are thousands of data in the spreadsheet so I will just show part of it to explain.

    Graph sample 2.png
    Graph sample 3.png

    This upper image above shows a magnified portion of my original 'graph' (actually scattered points). The left column data represents the x-axis and it is ascending as I want, but the right column data isn't only ascending which I have to prevent it. Therefore, I need a best fit line to estimate the values.

    After creating the best fit line, I have to retrieve the values on the y-axis based on the best fit line and intervals on x-axis (e.g. in every 0.005).

    I am neither supposed to rearrange the right column in ascending order (as I have to keep the left column ascending too) nor swap the data. So I guess I have to do it on the chart with the best fit line.
    Last edited by DK11; 03-02-2020 at 01:54 PM.

  4. #4
    Registered User
    Join Date
    03-02-2020
    Location
    UK
    MS-Off Ver
    Microsoft 2019
    Posts
    7

    Re: How do I create intervals from a best fit curve?

    The sample file is attached here.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,323

    Re: How do I create intervals from a best fit curve?

    Do you have an example calculation you can show? I am not certain I understand what you need to do.

    The only difference between your first picture and your second picture is that you have connected the points in the chart with lines. Which makes me think that maybe this is a basic linear interpolation problem. If this is correct, then the "easiest" way I have found to solve linear interpolation problems in a spreadsheet is to transfer the data to my computer running Gnumeric which has a build in INTERPOLATION() function. =INTERPOLATION($A$1:$A$16,$B$1:$B$16,desired_x_value,0) makes short, easy work of a linear interpolation problem.

    However, I also recognize that Excel so dominates the spreadsheet market, that many are required to use Excel, in which case I recommend an approach like this: https://www.excelforum.com/excel-for...ml#post5262186 This approach could easily be adapted to a scenario where you wanted to "fit" a line through 3 or 5 or whatever points for each segment.

    Is something like that what you are looking for, or am I misunderstanding something?

  6. #6
    Registered User
    Join Date
    03-02-2020
    Location
    UK
    MS-Off Ver
    Microsoft 2019
    Posts
    7

    Re: How do I create intervals from a best fit curve?

    Sorry please ignore the second picture I should't upload it.

    Here is a more simplified example.

    Graph sample 4.png

    The leftmost column is the x-axis value and the column next to it is the y-axis value, based on the actual raw data. The columns on the right-side of the graph are based on the best fit line(this is just a simplified example so I look into the best fit line and estimate them but I shouldn't do it in this way if there are 1000+ intervals). Is there a way to set the intervals on the x-values, and the system can achieve corresponding y-values based on the x-axis intervals according to the best fit line?

    Thank you for your suggestions anyways I will look into them.

    DK11
    Last edited by DK11; 03-02-2020 at 06:33 PM.

  7. #7
    Registered User
    Join Date
    03-02-2020
    Location
    UK
    MS-Off Ver
    Microsoft 2019
    Posts
    7

    Re: How do I create intervals from a best fit curve?

    I saw your previous post and I think the method is similar to what I want. However, I have to do this with the best fit line...not from the original raw data.

    The reasons I have to use the best fit line and set increment on x-axis are because I have to compare three different graphs which have different x-values, and their y-values are not always increasing. So the best fit lines are modelling their original graph, and I can set the same increment/interval on the x-values of the three best fit line graphs, and get the corresponding y-values, then I can get a mean value of them based on the same increment.

    I hope this will explain better than before
    Last edited by DK11; 03-02-2020 at 06:42 PM.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,323

    Re: How do I create intervals from a best fit curve?

    Again, it is difficult from a picture to tell what you are trying to do. I can see the red curve in the chart that "smooths" the data, but I see no indication of how it got there. It looks somewhat like a 4th or higher order polynomial fit to the entire data set (which could be done in the spreadsheet using the LINEST() function). Could your question really be how to perform a polynomial regression on the data?

  9. #9
    Registered User
    Join Date
    03-02-2020
    Location
    UK
    MS-Off Ver
    Microsoft 2019
    Posts
    7

    Re: How do I create intervals from a best fit curve?

    Yes it is a high order polynomial fit line but it is just an example (I will use other best fit lines).

    The problem is not how to get the best fit line, it can be any types of best fit, nor perform polynomial regression of the data

    the problem is how to get the corresponding values from the best fit line.


    Let's say, is it possible to copy the best fit line from the original graph, and create a graph for the best fit line only, and transfer the best fit line graph into two columns of values(x- and y-values) which based on intervals of x-values(reference), and achieve the corresponding y-values(final result)?

    Sorry for my unclear question.

    edit:
    I believe =LINEST() is not what I need as the curve should be non-linear and the function should give a linear result?
    Last edited by DK11; 03-02-2020 at 07:09 PM.

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,323

    Re: How do I create intervals from a best fit curve?

    Yes, it is possible to have the chart display the equation text for the best fit line, then copy that text into the spreadsheet, parse out the coefficients and then use that to calculate y at any x. However, in alluding to the LINEST() function, I generally prefer to generate the coefficients directly in the spreadsheet using the LINEST() function.

    Help file (take careful note in the Remarks section where it talks about how to regress a 3rd order polynomial): https://support.office.com/en-us/art...a-fa7abf772b6d
    A recent discussion: https://www.excelforum.com/excel-cha...ml#post5287071

    Is that most of the question -- how to move the regression from the chart to directly performing the regression in the spreadsheet?

  11. #11
    Registered User
    Join Date
    03-02-2020
    Location
    UK
    MS-Off Ver
    Microsoft 2019
    Posts
    7

    Re: How do I create intervals from a best fit curve?

    Quote Originally Posted by MrShorty View Post

    Is that most of the question -- how to move the regression from the chart to directly performing the regression in the spreadsheet?
    Yes I think that is my question. I will look into the your advice first and see if they help.

    Thanks a lot.

+ 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 create bell curve chart
    By Arpita_Excel in forum Excel General
    Replies: 2
    Last Post: 07-17-2015, 01:45 AM
  2. Replies: 2
    Last Post: 02-19-2015, 02:00 PM
  3. Create the s curve based on dates
    By velu130468 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-04-2014, 10:13 AM
  4. Display Mean and Confidence Intervals on Bell Curve
    By cp41 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 11-26-2013, 06:32 PM
  5. [SOLVED] Create bell curve
    By Starkey in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-22-2013, 08:13 PM
  6. How to create Bell Curve
    By Madan in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-30-2009, 09:09 AM

Tags for this Thread

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