max value of curve

[na19]

Hi there,
I want to find the max value of curve which is max between two given values of y .

[FDibbins]

Please do not post duplicate threads - I have closed the other 1.

Do you know the formula for this?

[na19]

No just the data points

[MrShorty]

I think there are generally two approaches to this kind of problem. Either you start with a regression where you fit your data to a curve, then use strategies from calculus to find the maximum value of the regression equation. Or you use an interpolation approach using some kind of cubic or other spline function. Without knowing more about the data you have and the approach you want to take, I'm not sure we can be very specific.

[na19]

The equation of my data is dependent on more than one variable. So I can't directly use the given equation to find maxima using calculus. And when I tried to curve fit using polynomial trend curve the equation of trend line curve doesn't follow the given data curve. I have attached the file as u can see the max value of y lie between the value of x 30 and 40. Which I want to calculate.

[MrShorty]

Is a polynomial the only regression equation you have tried? I know that polynomials are often a default first equation type to try, but they are far from the only possibility. A quick look at the sample data set suggests that a hyperbola might be a good fitting equation (though it will require implementing a non-linear regression technique): https://tutorial.math.lamar.edu/Clas...yperbolas.aspx

Another strategy I see is to select 3 or 4 points that encompass the maximum, fit those points to a quadratic or cubic polynomial, then solve that equation for the maximum. The first step is to select the desired points, then perform the regression, then solve for the maximum.

Extend this far enough, and it essentially becomes a spline interpolation problem. The main obstacle to this in Excel is that Excel does not have a built in spline interpolation (or any interpolation function). If you can use a spreadsheet other than Excel, Gnumeric has a built in spline interpolation function that can work. If you must use Excel, you will need to make your own spline function or find someone else's (see discussion here: https://www.excelforum.com/excel-gen...ta-points.html ).

You indicated that your real problem has multiple input variables, but your sample shows only one input variable (x). Have you over-simplified your example?

Ultimately, this kind of problem usually reduces down to some kind of A) choose a regression equation B) perform the regression C) solve the result for the maximum algorithm. Of course, there are multiple possibilities within that framework, but I'm not sure we are in a position to choose the final strategy for you. Does any of this suggest a strategy you would like to use for this? If you help us understand the strategy you want to use, then we should be able to help you program that into the spreadsheet.

[na19]

Thanks for the suggestion Mr Shorty. Using quadratic interpolation , i have been able to calculate the maximum of curve for the given data points.

[MrShorty]

Glad to help.