Excel Trend Line
Excel Trend Line
(OP)
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?





RE: Excel Trend Line
thread770-179377: Excel Curve Fit Lookup
thread770-214997: Multivariable regression in Excell
thread770-184726: Excel Curve Fit Coefficients
RE: Excel Trend Line
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.
RE: Excel Trend Line
Thanks for all the great input.
RE: Excel Trend Line
RE: Excel Trend Line
Using Denial's steps I have made Excel spreadsheet – see attachment. It seems to work like a charm
As Denial pointed out it was "an interesting little problem".
Regards,
IV