+ Reply to Thread
Results 1 to 32 of 32

Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

  1. #1
    Registered User
    Join Date
    06-20-2014
    Location
    Geraldton, Australia
    MS-Off Ver
    2013
    Posts
    36

    Question Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Hey guys,

    Posting from work about a feature we would like to introduce to one of our worksheets.

    We have 2 values (x and Y) that we are manually entering as a result from visually looking at a graph trendline generated from 4 data points of x and y.

    I need some VBA code to automatically determine these values.

    I have spent quite a lot of time looking around and reading about cubic spline, extracting coefficients on polynomial regression, interpolation and so on.

    Can someone please point me in the right direction or even have a look at the worksheet itself.

    Worksheet attached. (I have highlighted the values in yellow that we need to automatically determine.

    Any feedback is greatly appreciated.

    Cheers!

    Michael
    Attached Files Attached Files

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    You can use the Linest function to get the formula from the 3rd order fit.

    The use that to test 100 different X values to determine highest Y.
    Use Match to locate the X value used.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    The use that to test 100 different X values to determine highest Y. Use Match to locate the X value used.
    Or use calculus.

    Extrema occur at the endpoints of a function or at "critical points" (where the slope/derivative=0) http://www.mathsisfun.com/calculus/maxima-minima.html
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    06-20-2014
    Location
    Geraldton, Australia
    MS-Off Ver
    2013
    Posts
    36

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Thank you so much Andy, thats exactly what I needed.

    Really appreciate your help, you've saved me a lot of time.

  5. #5
    Registered User
    Join Date
    06-20-2014
    Location
    Geraldton, Australia
    MS-Off Ver
    2013
    Posts
    36

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    I Just noticed a small problem,

    If the calculated values from the data I have entered aren't in a certain (ascending?) order, the Y value using array formula does not calculate correctly.

    How do I correct this?
    Attached Files Attached Files

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Update the formula to use the MIN and MAX X values rather than the First and Last.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-20-2014
    Location
    Geraldton, Australia
    MS-Off Ver
    2013
    Posts
    36

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Cheers Andy,

    I did have a go at that, but I couldnt quite get it to work.

    Again, I really appreciate your help.

    Thank you.

  8. #8
    Registered User
    Join Date
    05-04-2015
    Location
    Viana do Castelo
    MS-Off Ver
    Microsoft Office 2011 (MAC)
    Posts
    17

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    I've tried to apply your logic to my problem but it didn't worked...

    I have a 4th order polynom... I have followed the logic but it keeps me giving the wrong Y value!
    I obtain 1.71 and it should be 1.83! !

    Can you help me with this please??
    I send my file in attach!

    Kind regards

    ( http://www.excelforum.com/excel-char...ml#post4306966 )
    Attached Files Attached Files
    Last edited by danielneivaamorim; 02-03-2016 at 06:27 AM.

  9. #9
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Wait... you're fitting a 4th degree polynomial with 4 data points?

    You need two points to uniquely define a line (1st degree poly.)
    3 pts to define a parabola (2and deg. poly.)
    4 pts to define a cubic (3ird deg. poly.)
    ...and 5pts to define a 4th deg. polynomial
    Last edited by joe31623; 02-03-2016 at 06:46 AM.
    <---If my answer helped, please click *

  10. #10
    Registered User
    Join Date
    05-04-2015
    Location
    Viana do Castelo
    MS-Off Ver
    Microsoft Office 2011 (MAC)
    Posts
    17

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    I have 5 pts in my datasheet!!

    Those are in red (f14:j14) = X values ; (f16:j16) = Y values

  11. #11
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Sorry... I missed that.

    https://en.wikipedia.org/wiki/Polynomial_regression
    MIT also has some lectures on linear algebra that clearly explain how to do it on YouTube. There are many resources.
    Last edited by joe31623; 02-03-2016 at 06:47 AM.

  12. #12
    Registered User
    Join Date
    05-04-2015
    Location
    Viana do Castelo
    MS-Off Ver
    Microsoft Office 2011 (MAC)
    Posts
    17

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    The problem is that I want to set up the datasheet to work automatically!
    Right now I know the expected result is 1.83 but after I want to input data and to find the maximum value in the chart.

    Is there a way to do this automatically?

  13. #13
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Yes. I would start with 1-deg polynomial (a line), then 2and, ..., then 4th.

    This shows how to find the coefficients of the 3ird deg. polynomial with 4 datapoints. It's just a generalization to find nth degree polynomial coefficients with n+1 x-y points.

    https://www.math.nmsu.edu/~breakinga...olynomial.html

    Is this homework?

  14. #14
    Registered User
    Join Date
    05-04-2015
    Location
    Viana do Castelo
    MS-Off Ver
    Microsoft Office 2011 (MAC)
    Posts
    17

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    This is not homework!!

    I'm creating a new Excel sheet to manage my test results at the Lab where I work at.

    Right now we have an old Excel sheet, and it calculates the things good, but the sheet is too old and it is very very very confusing! So confusing that I cannot understand how it calculates this. In fact it links to 4 different hidden sheets to calculate the maximum value in the chart...

    So I want to create a brand new, more simple and more intuitive!

    I have already figured how to find the coefficients, and they are correct (they match with the chart equation).
    How can I find the maximum value of the polynomial trendline line now?

    Thanks for the help

  15. #15
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    If you have the coefficients, most of your work is probably done. Take the derivative of your polynomial and set it to zero. Hopefully there will be one real solution.

    If you need help with an algorithm that finds that x-value corresponding to where the derivative is zero, I can help.

    Note that it may also be helpful to use the second derivative of the polynomial as well because that will tell you if it's a maxima or a minima.

    Are you familiar with calculus? If not, I can help you with the derivatives of a polynomial.

  16. #16
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Daniel,

    More concisely,

    Determine x0 such that x0 solves the equation: P'(x0) = 0, where P' is the derivative of P. This can be done with your algorithm of choice: https://en.wikipedia.org/wiki/Root-finding_algorithm

    ...taking a look at your graph, it looks like you're getting the solution: P'(x0) = 0 for which x0 is the minimum and not the maximum. You can test if P''(x0) <0, where P'' is the second derivative of P. I'm guessing P''(x0)>0, where P(x0) = 1.71, which means x0 occurs at a minima instead of a maxima. You need x1 such that P''(x1) < 0 and P'(x1) = 0 -- then P(x1) = 1.81 (or close to it). Usually root-finding methods have outcomes that are determined by your initial guess. So if you choose an initial guess equal to the first datapoint, you'll get root: x0 (a minima). If you choose an initial guess right in the middle of the data, that is, x1_guess = ((last datapoint) - (first datapoint))/2, you'll get the root you're looking for: x1 (described above with respect to P'' and P').

    The advantage of Method 1 is it's easy to understand and requires an "initial guess" you're hesitant about.
    The advantage of method 2 is that it doesn't require you to explicitly calculate P(x) and doesn't require initial guesses. It would be a slightly faster algorithm but it may be more difficult to understand. The video does a pretty good job of explaining it in my opinion.

    ...but do what shg says before you delve into anything I wrote above.
    Last edited by joe31623; 02-04-2016 at 11:21 AM.

  17. #17
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    An alternative would be to use LINEST to do a 4th-order fit, and the Change event to trigger a macro that uses Solver to find the maxima between the end values.
    Entia non sunt multiplicanda sine necessitate

  18. #18
    Registered User
    Join Date
    05-04-2015
    Location
    Viana do Castelo
    MS-Off Ver
    Microsoft Office 2011 (MAC)
    Posts
    17

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Some help would be appreciated!

    I send you a sheet with all I have done.

    I have seen in another post a solution but it isn't working for me!

    I have calculated the coefficients of the Y(x) and as well the coefficients for the Y'(x).

    Now what should I do?

    Thanks for the help


    Quote Originally Posted by joe31623 View Post
    If you have the coefficients, most of your work is probably done. Take the derivative of your polynomial and set it to zero. Hopefully there will be one real solution.

    If you need help with an algorithm that finds that x-value corresponding to where the derivative is zero, I can help.

    Note that it may also be helpful to use the second derivative of the polynomial as well because that will tell you if it's a maxima or a minima.

    Are you familiar with calculus? If not, I can help you with the derivatives of a polynomial.
    Attached Files Attached Files

  19. #19
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Now what should I do?
    As I explained in your other thread, and as Joe31623 explains above, the next step is to find the roots (or zeros) of Y'(x). Unless you want to program in your own root finding algorithm (see Joe's link above) or use the complex, closed form equations for the roots of a cubic polynomial (see wikipedia's entry for cubic polynomial), the easiest way is probably to use Solver or Goal Seek.

    1) Enter a reasonable "guess" for x at the max (14 seems like a good guess).
    2) Compute Y'(x) at this guess of 14
    3) Call Solver/Goal seek and tell it to set Y'(x) to a value of 0 by changing x.
    4) Check the result to see if it looks like it converged on the correct root (remember there are potentially 3 roots for a cubic polynomial -- your example shows 2 in the region of interest).
    4a) If the root is correct, compute Y(x) and you are done
    4b) If it converged on the wrong root, enter a new, different guess for x and go back to step 3.

    This process gave me a maximum Y of 1.834 at X=14.25

  20. #20
    Registered User
    Join Date
    05-04-2015
    Location
    Viana do Castelo
    MS-Off Ver
    Microsoft Office 2011 (MAC)
    Posts
    17

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    The thing is I don't want to assume values!
    I want it to calculate automatically...

    I have tried a formula but it give me the wrong number and I don't understand why...

  21. #21
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Daniel,

    I explained why you're getting the wrong answer in my previous post, but it may have been convoluted in a lot of other information that I now understand is irrelevant to your problem.

    When using any algorithm to solve roots with a polynomial of degree 4, you can get anywhere between 1 and 3 (real) solutions. Looking at your data, you have two minima and one maxima. That means you have three solutions for roots of P'. That means you will get a different value depending on what your "initial guess" is. The "initial guess" is unavoidable in root-finding algorithms. That said, you could automate the initial guess, given assumptions or use several initial guesses to find all the roots and choose the maxima instead of the minima.

    I'll elaborate on automating the initial guesses and using several initial guesses.

    Automating the initial guesses:
    -If your data always has a peak in the middle, use your initial guess as the midpoint of your x-data.

    Using Several initial guesses:
    -You can partition the range of x-values into 6-10 initial guesses for x. You should get 3 unique (or approximately unique with rounding/residual error) values when you run the algorithm for finding the roots 6-10 times.

    Again, you can check if a root, x0, of P' is a minima or maxima of P using P''(x0) b/c P''(x0) < 0 if x0 is a maxima for P.
    Last edited by joe31623; 02-04-2016 at 11:09 AM.

  22. #22
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    ...remember -- you didn't get the wrong answer when you found 1.71 instead of 1.83. You just found the wrong root (a root at the minima instead of the maxima). Again, the root a root-finding algorithm finds is dependent upon the initial guess (especially when there are multiple roots -- see my most-recent post).

  23. #23
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Quote Originally Posted by danielneivaamorim View Post
    The thing is I don't want to assume values!
    I want it to calculate automatically...
    I have tried a formula but it give me the wrong number and I don't understand why...
    As near as I can tell, your existing formula is computing Y at only one X point, and trying to determine the "max" of one point. Your formula is not working, because it does not seem to evaluate the polynomial at multiple points.

    Perhaps the intention behind the formula was something similar to what Andy Pope suggested to the OP of this thread in post #2. Kind of a "brute force" algorithm where you evaluate the polynomial at multiple points and use the MAX function to estimate the maximum. As one who detests single cell mega formulas, if I were trying to automate this kind of algorithm, I would have a couple of helper rows/columns where one row/column contains several x values spanning the desired range, and the other row/column computes the polynomial at those x values. Then a simple =MAX() function should find the estimated maximum.

    If you want to continue with the calculus approach: Did you understand the manual process I proposed using Goal seek? If not, I would suggest that you will need to understand how that works manually before automating it.

    In your other thread, I pointed you to a tutorial I put together for programming Newton Raphson algorithms to find roots of polynomials using circular references and iterative calculations. If you are interested in programming your own root finding algorithm, I would suggest you understand how that spreadsheet works.

    In another thread (http://www.excelforum.com/excel-prog...ml#post4239587 ) I discussed with a user how to automate the goal seek function using VBA. If you prefer to automate goal seek to find the roots, this should be a useful discussion to understand.

  24. #24
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    In an unmerged cell, confirmed with Ctrl+Shift+Enter:

    =MAX(SERIESSUM(F26 + (J26-F26) * (ROW(INDIRECT("1:101")) - 1) / 100, 4, -1, M31:P31)) + Q31

    (Returns 1.834)
    Last edited by shg; 02-04-2016 at 04:07 PM.

  25. #25
    Registered User
    Join Date
    05-04-2015
    Location
    Viana do Castelo
    MS-Off Ver
    Microsoft Office 2011 (MAC)
    Posts
    17

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    That formula isn't working for me... I'm working in Excel 2013 and in Portuguese so probably its a mistake of translation!

    I'm getting crazy with this!!!!!!

    But thanks for the all the help and support guys!
    As soon I get the answer I'll tell you.
    Meanwhile I'll try all the ways described in the other posts!

    Quote Originally Posted by shg View Post
    In an unmerged cell, confirmed with Ctrl+Shift+Enter:

    =MAX(SERIESSUM(F26 + (J26-F26) * (ROW(INDIRECT("1:101")) - 1) / 100, 4, -1, M31:P31)) + Q31

    (Returns 1.834)

  26. #26
    Registered User
    Join Date
    05-04-2015
    Location
    Viana do Castelo
    MS-Off Ver
    Microsoft Office 2011 (MAC)
    Posts
    17

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    The formula now works but I still have the wrong value...

    What the hell I'm doing wrong? And why do you obtain the value and I don't???

    And without ctrl+shift+enter i obtain 1.7 and with ctrl+shift+enter I have an absurd value!! (cell N45 and N46)

    WHY???
    Attached Files Attached Files

  27. #27
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    You're missing a pair of parentheses. Try copying the formula from the post.

  28. #28
    Registered User
    Join Date
    05-04-2015
    Location
    Viana do Castelo
    MS-Off Ver
    Microsoft Office 2011 (MAC)
    Posts
    17

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    And it works like a charm!

    Thanks a lot for all the help guys!!

    I'll be around this forum for now on!

    Greetings from a Portuguese in Mozambique trying to learn Excel!

    Quote Originally Posted by shg View Post
    You're missing a pair of parentheses. Try copying the formula from the post.

  29. #29
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    You're welcome.

  30. #30
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Three random observations:

    1. The use of INDIRECT makes the formula volatile. That would be easy enough to eliminate, but doing so would benefit from the use of a dynamic named range ("myCol") that refers to the column in which the formula appears to make the formula more robust.

    2. There's a danger in fitting a nth-order polynomial to n+1 points: The fit will always be perfect. That means any measurement error can be dramatically amplified. It might surprise you if you extend your trendline several periods forward and back.

    3. The shape of the curve is not polynomial (nothing in nature is). At a glance, I didn't see a math model approximation on the literature, but it looks more like better fit to a segment of a rational function with some sloped asymptotic behavior left and right (can't curve fit that with LINEST). I did see a method in the literature that uses a parabolic (2nd order) fit, which would make joe31's approach more attractive.

  31. #31
    Registered User
    Join Date
    05-04-2015
    Location
    Viana do Castelo
    MS-Off Ver
    Microsoft Office 2011 (MAC)
    Posts
    17

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    Thanks for all the considerations Sir!

    Just another doubt...

    In math now I know the following:

    y= 1.83 (max value of polynom trendline)
    I have my equation coefficients calculated through LINEST()

    So now I want to calcule the corresponding X value for y=1.83.

    I know that: 1.83 = Ax^4+Bx^3+Cx^3+Dx^2+Ex+F : and that formula would give me the value of X for the corresponding Y.

    Is that a way to do it automatically?
    I've searched on the internet and a lot of people suggest SOLVER or GOAL SEEK but I want to calculate it in an automatic way.

    Thanks again.

  32. #32
    Registered User
    Join Date
    05-04-2015
    Location
    Viana do Castelo
    MS-Off Ver
    Microsoft Office 2011 (MAC)
    Posts
    17

    Re: Obtaining maximum value from a polynomial x y scatter chart without 3rd party software

    I think I have found an answer but I would like to know your opinion about it.
    I applied from a file attached in post from Andy Pope (#2).

    I want to know your opinion because in the older excel sheet I get the values a bit different (instead of 1.830 I got 1.834).
    Is this normal?

    It's not a big problem but I would like to understand why. I think it might be related with the Excel numerical corrections and interpolations no?

    Thanks a lot for all the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Maximum number of data points for an xy scatter chart?
    By Rich F in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-11-2017, 04:03 PM
  2. 3rd party software or similar?
    By pasteis in forum Excel General
    Replies: 3
    Last Post: 06-08-2012, 08:04 PM
  3. Scatter plots - maximum ordinate points
    By Bobb09 in forum Excel General
    Replies: 0
    Last Post: 06-10-2011, 06:48 PM
  4. Secure lock & hide columns using 3rd party software
    By Snoopy2003 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2011, 06:01 PM
  5. [SOLVED] How to assign datalabels to a scatter chart, obtaining the labels.
    By Jos Koot in forum Excel General
    Replies: 1
    Last Post: 04-22-2005, 08:06 AM

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