# Idealized Bilinear Curve

1. ## 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   Register To Reply

2. ## 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?  Register To Reply

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 .  Register To Reply

4. ## 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.  Register To Reply

5. ## Re: Idealized Bilinear Curve

Hi Behnam
I have the same problem in drawing billinear graphs and I hope the explanation helps me  Register To Reply