+ Reply to Thread
Results 1 to 4 of 4

Interception of two non linear graphs

  1. #1
    Registered User
    Join Date
    02-07-2017
    Location
    Osijek, Croatia
    MS-Off Ver
    2010
    Posts
    3

    Interception of two non linear graphs

    Hallo all,

    this is my 1st time ever to post a thread on any forum so I hope I won't write anything inappropriate (or bad written).
    I am trying to make an Excel data that automatically shows the interception point of two curves.

    I am posting my example and I would like you to comment on it.
    It seems to me that this solution does not always give the same result for the interception point.
    Example:
    if I put another start-end in the column "input"
    - although curve "A" is always the practically same curve no matter where it begins or ends - the input cells are there to make the x-value grow equally in the same step
    -by changing the start and end the step changes also but the curve does not change much
    - i deleted all formulas of curve A and B because the file was too large.

    Please comment
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Interception of two non linear graphs

    Two basic approaches to this kind of problem that I see -- regression or interpolation.

    Your current approach looks like a regression approach.
    1) Assume a regression equation for each curve (your current spreadsheet is assuming a straight line y=mx+b regression for each curve).
    2) Regress parameters for each equation.
    3) Solve the equation y=fA(x)=fB(x) [can be rewritten as 0=fA(x)-fB(x)]. Your current approach looks like mA*x+bA=mB*x+bB, which is easily solved explicitly for x. It would not be uncommon to encounter forms of these equations where a closed form solution is not possible and one would need to resort to numerical "root finding" algorithms to find the solution(s).

    It is unfortunate that you deleted the formula information for curves A and B, because those formulas might give some indication of what fA(x) and fB(x) should look like. Curve A looks like a near perfect quadratic (y=ax^2+bx+c), but I cannot readily tell what curve B should be. Knowledge of the real world process being modeled can also provide guidance as to the desired equation. (Taking a wild guess, is this a fluid mechanics/hydraulics/pump performance problems where Q is flow in liters/second and H is pump head in meters?)

    Not a complete solution, yet, but this is the basic outline of one method for solving these kinds of problems. Does this sound like a workable approach? What equations would you use for fA(x) and fB(x)? Are you familiar with Excel's Solver and/or Goal Seek utilities as tools for finding roots of equations?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    02-07-2017
    Location
    Osijek, Croatia
    MS-Off Ver
    2010
    Posts
    3

    Re: Interception of two non linear graphs

    You guessed it right for this being a fluid mechanics/hydraulics/pump performance problem. Curve A uses a few constants (roughness, diameter) and coefficients (loss coefficients (Greek letter "x"), lambda, Reynolds number etc.). It may look like a quadratic function but I am not sure about this.
    I have never tried these utilities you mentioned because I am suspicious about the safety of the products + I am working mostly on my work PC and I can't download something that needs a licence.

    I see that my solution is maybe too easy and too incorrect for bigger curves because the regress parameters only make a linear equation from both curves.
    It would be very helpful if I could write a formula that includes this same regress method but just for the 2 nearest "Q"-s (flow or x-data in my example).
    If I put that my interception is "somewhere near the value X=5 ---- For instance to try to implement a formula that makes the same regression for the bigger and lower X. Any idea how to make this ?

    I thought that there maybe is some way to find the interception directly on the chart in a way that's similar adding trend lines or other lines/points on the chart without the need to input more data in the "select data" .

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Interception of two non linear graphs

    I have never tried these utilities you mentioned because I am suspicious about the safety of the products + I am working mostly on my work PC and I can't download something that needs a licence.
    If you are referring to Solver and Goal Seek, both of these are features built into Excel.
    Goal Seek should be in the Data menu under Data analysis tools: help file: https://support.office.com/en-us/art...rs=en-US&ad=US
    Solver comes with Excel (though it is not always included by default in the installation): https://support.office.com/en-us/art...b-f63e45925040

    So you would like a linear interpolation approach.

    It might be odd on an Excel forum, but it is too bad that you are not doing this in QuattroPro (or other spreadsheet with a built in linear interpolation function). This would be straightforward
    1) Guess a value for y at the intercept
    2) @LINTERP(known_x_A,known_y_A,new_y)-@LINTERP(known_x_B,known_y_B,new_y)
    3) Use the applications goal seek/solver equvialent to set step 2 to a value of 0 by changing your guess at y.

    It is still straightforward in Excel, but step 2 in necessarily more complicated. I have a couple of examples on the forum of how I would recommend setting up a linear interpolation.
    http://www.excelforum.com/showthread...=1#post4421460
    http://www.excelforum.com/showthread...=1#post3904113

    The basic idea is to
    1) guess at y
    2a) Perform a straight line regression for each pair of consecutive points (My examples use the LINEST() function for this, but SLOPE and INTERCEPT could also work) for each curve.
    2b) Use a lookup function to extract slope and intercept for each curve at your guessed Y value
    2c) Compute x at y for each curve and subtract.
    3) call goal seek/Solver and have it set the result of 2c to a value of 0 by changing y.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel VBA Interception and Implicit Intersection and VLookUp
    By Doc.AElstein in forum Tips and Tutorials
    Replies: 11
    Last Post: 03-14-2017, 08:42 PM
  2. [SOLVED] LINEST - Non-Zero Y-Interception
    By Wes-at-UoB in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-01-2015, 05:39 AM
  3. Replies: 1
    Last Post: 10-24-2014, 12:20 AM
  4. multiple graphs on one screen and moving graphs
    By mufan in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-12-2014, 04:49 PM
  5. [SOLVED] linear and exponential graphs and x,y intercepts
    By [email protected] in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-14-2011, 01:10 AM
  6. [SOLVED] interpolating non-linear curves in excel graphs
    By chris in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 06-19-2006, 10:15 PM
  7. line delete interception
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2006, 11:55 AM

Tags for this Thread

Bookmarks

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