MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 307|Reply: 5

Interception of two non linear graphs

[Copy link]

1

Topics

5

Posts

19

Integral

Member

Rank: 2

Integral
19
Post on 2-9-2017 05:10:47 | All posts |Read mode
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








  • Interception_ml.xlsx
    (16.0 KB, 0 views)
    Download

  • Reply

    Props Report

    13

    Topics

    1065

    Posts

    2355

    Integral

    King

    Rank: 6Rank: 6

    Integral
    2355
    Post on 2-9-2017 13:44:19 | All posts



    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?
    Reply Support Opposition

    Props Report

    13

    Topics

    1065

    Posts

    2355

    Integral

    King

    Rank: 6Rank: 6

    Integral
    2355
    Post on 2-10-2017 03:50:28 | All posts



    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?











    Reply Support Opposition

    Props Report

    1

    Topics

    5

    Posts

    19

    Integral

    Member

    Rank: 2

    Integral
    19
     Author| Post on 2-10-2017 05:28:27 | All posts



    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 .
    Reply Support Opposition

    Props Report

    1

    Topics

    5

    Posts

    19

    Integral

    Member

    Rank: 2

    Integral
    19
     Author| Post on 2-10-2017 16:29:36 | All posts



    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 .




    Reply Support Opposition

    Props Report

    13

    Topics

    1065

    Posts

    2355

    Integral

    King

    Rank: 6Rank: 6

    Integral
    2355
    Post on 2-10-2017 17:03:03 | All posts



    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-USad=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 Support Opposition

    Props Report

    Points policy of this forum

    Archiver|Mobile|Small dark house|Contact us|Excel Bar

    GMT-5, 11-19-2017 16:22 , Processed in 0.196012 second(s), 20 queries .

    Powered by Discuz! X3

    © 2001-2013 Comsenz Inc.

    !fastreply! Top !return_list!