Excel Chart - Trendline Set as maximum position

Hi All,

I have a chart with is used to set limit of the maximum allowable lean.

There is a trendline which sets the maximum allowable lean depending on the value in Cell D34 and where the X Axis 'hits' the trendline. The x axis is set by a calculated ratio (Found in cell D34), the y axis figure should be found by following the x axis up to where it meets the trendline. Currently, this is done by a getting an approximate figure based on an auto cad drawing and then slightly adjusting that figure with a spin button until the X&Y axis's meet at the trendline

In the example sheet, Cell J31 is showing 1.12% and requires adjusting as it should be 1.07%.

I would like the axis lines to automatically at the trendline as I don't have to adjust it.

Is this possible?

Example attached.

The trendline data is in H44:I53
The data to get an approximate result is is H55:I237

I'm hoping is is possible to get rid of all the approximate data.

Thanks in advance

Originally Posted by MrShorty

How much of this is math/engineering and how much is specifically Excel programming?

Solution 1) It looks to me like we have a handful of tabulated points that were probably calculated using some engineering function somewhere. As an engineer, my first preference would be to find that equation. My first suggestion would be find a friendly neighborhood engineer who is knowledgeable in this field, get him/her to teach me the equation. Then, I could come back to Excel, program that equation into one or more spreadsheet cells, and then it is a simple matter of plugging x into the equation and the spreadsheet will spit out y automatically.

In the absence of a friendly neighborhood engineer (or other source education), I see two possibilities -- regression to come up with my own equation that describes the tabulated data points or an interpolation scheme to interpolate between data points.

solution 2) regression. The hardest part here is trying to decide what kind of equation I ought to use (my first guesses would be some kind of exponential decay type of formula or something more complex). Once I have chosen a suitable equation, then I use an appropriate linear (LINEST() in the spreadsheet) or non-linear (Solver) regression technique to perform the regression. Then I have a formula that describes the trendline and I can simply plug x into that formula to spit out y.

solution 3) interpolation. For this one, are you limited to Excel? Interpolation is much easier in a spreadsheet that has a built in interpolation function (tip: Excel is not one of those spreadsheets). Gnumeric has an interpolation function that can do both linear and spline interpolation that makes short work of interpolation problems (https://help.gnome.org/users/gnumeri...-INTERPOLATION ). If you could move this to Gnumeric and wanted to solve using linear interpolation, it would be as simple as [after sorting the trendline values by the x column] =INTERPOLATION(H44:H54,I44:I54,D34,0). Of course, interpolation is more complicated if you must use Excel. Some past examples:
https://www.excelforum.com/excel-for...ml#post5380675
https://www.excelforum.com/excel-for...ml#post5262186

Hopefully I have understood the question correctly. As noted, my preference would be to understand the maths/engineering behind the question so that I could enter the actual formula that relates x and y. In the absence of the true formula, I would make my best guess using either regression or interpolation (assuming, of course, that whoever is overseeing my work will accept either of those approximations). How would you like to proceed?

Thanks for the response. Unfortunately all you are give in the standard is the graph which shows the trendline. You are the. Told to calculate the ratio and from that figure, match up to the trendline for the allowable tilt. No figures are given at all. That’s not to say they don’t exist, but you’re not given them. I have attempted to get close to it but as you can see, it’s not perfect hence the Manual adjustment. I was hoping that the ration that line up with the fixed point (trendline) and from that we can get the %

Originally Posted by MrShorty

Which approach do you want to use -- a regression approach or an interpolation approach? Your pictures seem to imply an interpolation approach, so, if that is the approach you want to use, what questions do you have about my suggestions as far as implementing an interpolation approach?

I would assume that, if we can’t get it to find the % based on the trendline & ratio, then probably interpolation.

Originally Posted by MrShorty

Did you have any luck implementing the interpolation suggestions I mentioned? As I said, Gnumeric with its built in INTERPOLATION() function is by far the easiest if you are not limited to Excel.

No tried yet, I will be trying tomorrow. Unfortunately, I’m limited to excel.