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?

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.

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