Excel Split a Curve and Fit a trendline using VBA - - URGENT

Hello all,
Iam a civil student and for a lab test I need to solve about 450 excel sheets for my project.
The spreadsheet has X and Y data and curve is plotted. I need to split the curves into 3 parts based on slope change.
I can do it manually but I need an excel Macro which does this work. Could any one help me.

A sample is attached.

Also: http://www.ozgrid.com/forum/showthread.php?t=185862

Thanks Sir. This macro is good for only this data.
But Sir, If I have to run it on other sheets, the chart is being plotted with the range A2:A24 for 1st series always.How to make it dynamic for other charts too I want to split when the Rsquare value is less than specified. Your help is of great deed.

Thanks for your concern.

This works fine... i'll try to refine it further..

But is there a way which dynamically divides the chart into some number of parts depending on the slopes.
say sample 1 has 3 parts. 1st series of 5 data points, second series of 10 data points and third series of 15 data points.
say for sample 2, This may differ.

Originally Posted by shg

I think this is too slow to be practical, but it's entertaining to watch.

Thanks Shg. This sheet works very good. But for my project I want to learn more. Can i know more about Piece wise Linear approximation

Originally Posted by bsalv

see attachment

in that file, there is only 1 chart, so you should make a loop for your 450 charts.
Do that with the macro "FirstValues"
there you see in red
SplitCurve MyX, MyY, 3
With that instruction, you split the x and y values in 3 sections and afterwarts adapt the chart.
So you have to specify that 450 times.
If there is a certain structure in your data, that 'll be easy, otherwise ...

Please Login or Register  to view this content.

This works fine... i'll try to refine it further..

But is there a way which dynamically divides the chart into some number of parts depending on the slopes.
say sample 1 has 3 parts. 1st series of 5 data points, second series of 10 data points and third series of 15 data points.
say for sample 2, This may differ.

Originally Posted by shg

I think this is too slow to be practical, but it's entertaining to watch.

Its a huge VBA code, that I cannot understand. But just have a look at it, when the data is changed. I have changed the data and i gave 3 segments for ir to run.

Please find the attachment.

Originally Posted by bsalv

what do you want exactly ?
With my "SplitCurve MyX, MyY, 3", the macro choose the 3 segments to best fit the curve. That was 1-4,4-24,24-36 (see sheet2, with the regressionfunction).
Do you want now to say that it's not 1-4,4-24,24-36 but for example 1-5,5-15,15-30 ? So the solution 'll be less ideal !
Or do you want some points to be deleted before calculation ?

ooops...seems iam causing some irritation here. But sir, check out this attachment. If my next set of data is less than this size (36 rows), there is a run time error, which when tried to debug has error on this line.

Please Login or Register  to view this content.

.

Sorry for troubling. but iam learning more and more

Originally Posted by bsalv

Irritation no, confusion !
I suppose that you're not familiar with tables, so your data is no longer a table but normal data.
In the macro "FirstValues", you specify now the ranges of your X and Y-values in a way which is easier to understand.
To know the name of the graph, you run the macro "GraphNames" and you get the name of the graph and the address of the topleft cell of that graph (topleft cell = the cell with the topleft corner of your graph in it).
There is an important remark, your values in the columns A and C in your example are "strings" !!!
If you looked at your graph, that had a very funny shape, so that was already a first indication.
If you modified the format of the columns A and C to a number with 2 digits after the comma (format "0.00" or perhaps "0,00", depending on your international settings), you should have seen that they remained unchanged. 2nd indication that it were strings. (look how my number look like in columns A and C)

Now everything is ready to run the macro "FirstValues".

Now understood and is working fine for most of the data. The problem is with the number of data points for different samples.
As the present chart is based on some N1 values of (x,y) for sample 1 as in the sheet. Assuming N2 for sample 2, N3 for sample 3............
The chart works fine if N(i+1)>N(1) but it shows error if N(i+1)<N1.