Excel 2007 : Slope determination and translation along horizontal axis

Hi all,

I have a plot that looks like the one in the image below:

TEST_14.jpg

Basically, here's what I need to get accomplished:

1. Determine the initial slope (i.e. m1) of the curve.
2. Translate the slope two (2) units along the horizontal axis to get m2.
3. From the intersection between the slope m2 and the original curve, I need to obtain the y- (Bending moment) and x- (Angle of rotation) values at that point.

Is it possible to perform such operations in MS Excel? If it is, how do I go about doing it? The data file for the plot is included in the attachment.

Thanks!

I apologize, I can't download your test file, so I'll have to comment without seeing the data table.

1) Determine initial slope. I would probably use the LINEST function to get the equation for m1 (y1=m1*x+b1). The "hardest" part of this step is something I probably can't do for you, and that is determine how many points to include in this regression. Technically, you only need the first two points, but if the data are "noisy," I would worry that only two points would skew m1 too high or too low.

2) The translation step is easy. Go back to your early days of algebra, and you should recall that you simply need to substitute (x-2) into the equation for y1 to get y2 [y2=m1*(x-2)+b1].

3) Now that you have an equation for y2, the problem basically becomes one of solving a system of equations (again, going back to those days studying algebra). The equation for y2 is easy to come by. What to use for an expression for y is a different matter. A couple of possible approaches.
a) Use linear interpolation. Microsoft didn't provide a built in linear interpolation function, but you can build one yourself without too much difficulty. Others here are better at building this kind of function. It usually involves a MATCH function to locate the interval containing x, four instances of the INDEX function to return the endpoints of the interval containing x, and a linear function (TREND or build from the old point-slope form of a line function learned in algebra) to calculate y at the given x.
b) Use a polynomial to regress a function for the expected portion of the curve where y2 will intersect. The main difficulty with this choice is deciding what order polynomial to use and what data to include in the regression. The regression itself is easily performed using the LINEST function.
c) At this point, the easiest way to solve the system of equations might be to use Excel's built in Solver (assuming you installed the Solver option when you installed Excel). To do this, guess an x that you feel should be close to the point of intersection. In one cell, calculate y2 using that x. In another cell, calculate y using either approach a) or b) for the same x. Add a third cell that is simply the difference of those two cells. Then call Solver, and tell it to set this third cell to a value of 0 by changing the cell containing your initial guess for x. Be sure to check the result it comes up with to see that it is correct.

Hi,
See attached a solution
In Data sheet :
- define the equation of the line : as we have no explanation I choice a range which fit to the attached image
- interpolate the intersection of the moment vs angle curve and the line
Note : the x and y range for curve and line are named range, you can play by changing the range
In Graph2 sheet the chart as request
What about the formula for moment vs angle ?
How do you define the first line ?
Hope this helps
Best regards