Best Fit Curve

[niall493]

hi,
i want 2 plot a best fit curve. i have 3 different series of data plotted on a chart and want to put in an average curve. my x and y data are different for all 3 sets of data!

[teylyn]

Hi niall, welcome to the forum. Could you post a sample file?

You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

[niall493]

thanks. i hav a attached the file. i want to insert a best fit curve to the chart with 3 curves in it already!

[zbor]

I would say, just looking at it, that your green curve is medium already.
Not only by the fact that 100mm is middle of 50 and 150, but also it has most data (wich bring it to most reliable function) and it's smooth...

So, instead of looking all 3 of them, why don't you just deal with 100mm curve?

[niall493]

i'm not to sure how 2 explain it! i basically need to integrate the 3 graph's into 1 but i can't just take the 100mm curve as it is not really the medium!

[rwgrietveld]

I'm going to provide you with a solution and it look worst than it is !!

See attachement.

all is based on linear interpolation. I need the INDEX / MATCH to find points before and after the new X value.

=INDEX($B$3:$B$34,MATCH($M4,$A$3:$A$34,1),1)+($M4-INDEX($A$3:$A$34,MATCH($M4,$A$3:$A$34,1),1))*(INDEX($B$3:$B$34,1+MATCH($M4,$A$3:$A$34,1),1)-INDEX($B$3:$B$34,MATCH($M4,$A$3:$A$34,1),1))/(INDEX($A$3:$A$34,1+MATCH($M4,$A$3:$A$34,1),1)-INDEX($A$3:$A$34,MATCH($M4,$A$3:$A$34,1),1))

It can all be shortened, but I'have to go to bed It's 00:53

[zbor]

rwgrietveld, if you take median instead of average for your new data you'll see it's perfectly match with 100mm in relevant part (From 0 to 100 is irelevant because all curves overlaping).

I think that average is not good because point A25,B25 is so bouncing than rest of thata that it shouldn't determine graphs. And also, lack of points after that one particular.

The median can be used as a measure of location when a distribution is skewed, when end values are not known, or when one requires reduced importance to be attached to outliers, e.g. because they may be measurement errors. That's why I think median is better.

But in that case you get 100mm curve (again).

I would like to see shg opinion because he's probably best mathematician here, and this belongs more to math domain rather than excel. After we see what result should be would be easier to implement it.

[rwgrietveld]

zbor, I hear you and I (too) am not a statistical hero, but to me median is not correct as it will always take the middle value (100mm) in case of three values.

if you take median instead of average for your new data you'll see it's perfectly match with 100mm

Yes, by definition !! My feeling is that taking the median is not correct.

Then you through in all sorts of statistical terms that relate to correlation and frequency graphs. Again, I am not an expert, but I do not see the relevance. A simple avg on interpolated values looked good enough to me.

On the other point I agree, There are some arbitrary points, but I had to interpolate as non of the x-values is the same and then you'll end up with a margin of error.

I hope Niall shows up to give us some pointers.

[niall493]

thanks rwgrietveld,

i thought there was a basic tool i could use on excel to do this! I've talking to my lecturer and yes it is a maths prob so i'm a bit out of my depth. the curve you posted looks like what i need. is there any chance you could give me a quick explaination of how you did it? thanks a million for your help

[rwgrietveld]

For the interpolation you need to make sure that the new X is in between x1 and x0. The index / Match look up these values and determine the new Y Y=f(X).

Y = X0 + (X-X1)*(Y1-Y0)/(X1-X0)

Basically it's: y= a + bx where a=X0 and b (slope) =(Y1-Y0)/(X1-X0)

That's it. It looks difficult, but in fact its not.

[shg]

I never saw this post. niall, if you're still around, you can take all three series and stack them one below the other, and plot them as a single series. Then add a polynomial trendline, which produces a best fit the three.

If you need the coefficients, you can get them with LINEST.

If you know something about the phenomonon, you could choose a set of basis functions other than polynomials, and get a best fit to those.