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?

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.

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.

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?

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

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

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?

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?

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?