scientific calulations using scattter and trendlines

 Hi all, firstly I am wanting to plot a non linear XY data set (essentially 2 sets of nearly linear data with different slopes). then I would like to start working from the end pair of XY point with a linear trendline adding a pair to the trendlne fit at a time, examining the r value (correlation coefficient) to find where it begins to reduce (this being the end of the first linear set of data). Then spit the XY data set so that 2 series are plotted the first ending based on the last highest r value. Finally fit the 2 series with linear trendlines to get the equations and using simulataneous equations (here i suppose an input box for the equations would work) work out the intersect of the linear fit lines.

I can do this manually but have only managed to imlement a slider to work the data series fit by adding pairs of data points. this obviously takes time and then i have to fit the lines and carry out the simultaneous equation calcs.

I have never tried vba so this is quite a daunting task - any help here very much appreciated.

Steve

Hi Steve,

Rather than use VBA, I've developed a worksheet only solution, using dynamic named ranges, LINEST linear regression function, datatables, index/match to pick out the best results, and finally solving the simultaneous equations and plotting!!

Sorry I don't have time to explain the whole thing in detail here right now, but I've tried to add lots of comments in the attached excel file, and will be more than happy to respond to questions.

I would also appreciate comments and feedback from the rest of the community here on my hackjob solution.

Hope this helps!

Rick Williams

(P.S. I'll post now and see how to add the excel file in a minute... EDIT: Aha! Done - should be listed just under the reply heading)

Hi Rick many thanks for the excel sheet solution. I will look it over as soon as i get chance and let you know how i get on.

PS I agree that you cannot use the first 2 points as this will always be a perfect fit. I have implemented a slider to affect the plotted data and update the r squared value as it continues selecting paired data. But there is also a problem here in that the change also depends on how many points are fitted prior to the "bad" pair. I am also experimenting with using spline fitting whereby i select three pairs and get a fit and do this consecutively to find a breakpoint. Its not a simple task as far as i can see though. Maybe some mathematicians will have a solution.

Best regards

Steve