+ Reply to Thread
Results 1 to 5 of 5

Idealized Bilinear Curve

  1. #1
    Registered User
    Join Date
    12-09-2015
    Location
    Tehran
    MS-Off Ver
    2007
    Posts
    3

    Idealized Bilinear Curve

    Hi
    i'd like to draw a bi-linear idealized graph of a scatter chart type.The main point is the area under these curves should be the same.i tried to use the trend line but it seems it does not have the bi-linear type.
    I really really appreciate any help.
    BR
    Behnam
    PS:Merry Christmas
    Attached Images Attached Images
    Attached Files Attached Files

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

    Re: Idealized Bilinear Curve

    As you have already discovered, the chart is not going to perform this analysis for you automatically. I don't know how much help you are going to need, but here's how I would perform this analysis:

    1) Enter a value for the "hinge point" (for lack of a more technical term for the point where the two lines intersect).
    2) Separate the overall data set into two sets -- one set below the hinge point and one set above the hinge point.
    a) I expect you will want to figure out how to do this dynamically. There are a few strategies, the most common would be the use of "dynamic named ranges": https://support.microsoft.com/en-us/kb/830287 I would expect you to end up with 4 named ranges at the end of this step.
    3) Using the LINEST() function (https://support.office.com/en-us/art...a-fa7abf772b6d ), calculate the slope and intercept for the two lines.
    4) From these regression results, compute the desired "area".
    5) Compute the area of the raw data.
    6) Compare 4 and 5, and adjust the value for the "hinge point" until the area from step 4 and the area from step 5 are the same.
    6a) I will usually add a cell to compute the difference of step 4 and step 5, then
    6b) call Solver and tell it to set the cell from 6a to a value of 0 by changing the hinge point in step 1.
    7) Evaluate result to see if it is reasonable.
    8) If you want to plot the two lines, compute the start (0,0), hinge (hingex,hingey), and end point (xmax,y(xmax)), then add these ranges as an additional data series.

    With the problem broken down like that, what steps are you uncertain on?
    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
    12-09-2015
    Location
    Tehran
    MS-Off Ver
    2007
    Posts
    3

    Re: Idealized Bilinear Curve

    Dear MrShorty
    Thank you a lot for the comprehensive explanation.i tried a lot to do as you kindly said but did not succeed.
    I know it is too much of a request ,but could you please do an example on my file.because my 3 month task result depends on this .
    Thanks in Advance
    Attached Files Attached Files

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

    Re: Idealized Bilinear Curve

    You did not change much. All I could really see was adding a text box with a "hinge point" to the chart. As I noted, the chart is not going to do this analysis, so we need these values and analysis in the spreadsheet. You also did not explain where you got stuck.

    Since the most difficult part of this for me is generating the named ranges, I assumed that was where you got stuck as well. The sample shows one dynamic named range, as well as one of the regression functions, using a dynamic named range for one argument and an OFFSET() function for the other argument. You can decide if you want to make 4 named ranges like leftxvalues, or use the OFFSET() function in the LINEST() functions for the regression of the two straight lines. I did not define all four names, or both LINEST() functions, but there should be enough there, along with the help files and other internet tutorials, to see how to create a dynamic named range and how to use it in the LINEST() function.

    I am assuming that you know how to compute the areas. If you do not remember how to compute the area of a triangle and a trapezoid, you should be able to find that information on the internet.

    Note what I mention in G62. It might be easier to simply enter the desired x and y values for the hinge point in AA40:AB40, and ending pont in AA41:AB41, and simply use those three points to define the two lines. One would still need to know how you intend to compute the area of the "raw" data, but it might be easier to get to the "area under the idealized curves" part without needing to go through the LINEST() function.
    Last edited by MrShorty; 12-28-2015 at 01:31 AM. Reason: add attachment

  5. #5
    Registered User
    Join Date
    10-23-2016
    Location
    Mazandaran,Iran
    MS-Off Ver
    2016
    Posts
    1

    Re: Idealized Bilinear Curve

    Hi Behnam
    I have the same problem in drawing billinear graphs
    and I hope the explanation helps me
    Last edited by fallah.ce; 10-23-2016 at 08:31 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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