+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Slope determination and translation along horizontal axis

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2007

    Post Slope determination and translation along horizontal axis

    Hi all,

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


    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.

    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    North America
    MS-Off Ver
    2002/XP and 2007

    Re: Slope determination and translation along horizontal axis

    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.

  3. #3
    Forum Contributor
    Join Date
    Rhône Alpes, France
    MS-Off Ver
    Excel 2007

    Re: Slope determination and translation along horizontal axis

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1