Excel Trend Line 1

[vid071892]

I have 26 Mohr circles and need to find c and phi(cohesion and angle of friction)the only way I know to do this if by obtaining the line of best fit for data then getting angle of that line, but I don't know if it is possible to input all the data into the add trend line option for the chart. Any ideas?

 

[Clyde38]

Check these threads, they may help. . .
thread770-179377

thread770-214997

thread770-184726

 

[Denial]

Soil mechanics is most definitely NOT something I understand, so my apologies in advance if I am misunderstanding your problem.

Standard linear regression starts with a set of (X,Y) points, and attempts to determine a line of best fit Y=aX+b. This is called the "line of regression of Y on X", or the "line of prediction for Y". The important point here is that X is known exactly ("nonstochastic" in stats-speak). It follows from this that the "error" associated with any individual point is Y-Y' where Y' is the value predicted by the regression equation.

You are working in (S,T) space rather than (X,Y) space, where to speed up my typing I am using S for the stress "sigma", and T for the shear "tau". You have a set of circles, each characterised by a centre point (C)that lies on the S-axis and a radius (R). You have a set of (C,R) points, and you want to find the equation of the "common tangent of best fit" to the set of circles. So you do not directly have points through which you want to fit a line. The (S,T) points have to be calculated from the (C,R) data, and the calculation cannot be performed without assuming a value for the slope of the resulting common tangent line.

If this is in fact a correct paraphrasing of your problem, try tackling it as follows (using Excel).

(1) Create two cells, one for your assumed value of the slope "a", the other for your assumed value of the intercept "b".

(2) Set up a table containing one row for each of your data points. Col A to contain C, col B to contain R.

(3) For each row, in colC and colD calculate the (S,T) coordinates of the point on the circle where the slope of the local tangent is parallel to the assumed "common tangent of best fit".

(4) In colE and colF calculate the (S,T) coordinates of the point on the common tangent line that is closest to the point you calculated in step (3).

(5) In colG calculate the square of the distance between the point calculated at step (3) and the point calculated at step (4).

(6) Elsewhere on the spreadsheet, create a cell containing the sum of all the squared distances you calculated in step (5).

(7) Use Excel's SOLVER to minimise this sum by varying the slope and intercept of the comon tangent line (the cells created in step 1).

This will give you ONE tangent of best fit. However there is no unambiguous definition of THE tangent of best fit, which is why I laboured on about the standard linear regression approach in my introductory remarks.
» Presumably in your data both C and R should be regarded as stochastic?
» The "error distance" calculated in step (5) was taken perpendicular to the common tangent. It could just as validly been measured vertically. Or horizontally. Or at the angle between your tibia and your fibula.

An interesting little problem. Good luck.

 

[vid071892]

I tried LINEST and all others, the main problem is that my data is in 52 columns, 26 for the "Y" and 26 for the "X" values and since the circles are of different radii the columns are of different lengths and excel doesn't like that, I could create a single column of "X" and one of "Y" with all the values but that would take quite a while since there are 2660 entries in each but I just keep on trucking and hopefully finish this weekend.
Thanks for all the great input.

 

[msucog]

are you trying to find a singular "solution" for each one/set or trying to find an "average" of all the solutions?

 

[cdi12]

vid071892:
Using Denial’s steps I have made Excel spreadsheet – see attachment. It seems to work like a charm . Feel free to modify it accordingly. Additional calcs need to be added to keep X and Y scales equal.
As Denial pointed out it was “an interesting little problem”.
Regards,
IV