Calculate Max Valuem from Polynomial Chart

Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.excelguru.ca/forums/showt...lynomial-Chart

Note discussion at t he end of this old thread: http://www.excelforum.com/excel-prog...ml#post4306979 (note that this forum prefers when users not resurrect old threads, preferring, as you've done here, to start your own thread)

As I note in the other thread, dflak's solution proposed in 3 is "correct" for a 2nd order polynomial, so we only need to extend it to higher order polynomials. The basic idea:

1) Regress polynomial (4th order in this case)
2) Take 1st derivative of polynomial (which will be a 3rd order)
3) Find the roots or zeros of the 1st derivative to find the x values at the potential maxima.
4) Compute y at each root and determine which is the maximum.
5) Check the endpoints of your desired interval to make sure none of them is higher than the maximum in 4.

The key steps are 2 and 3 -- taking the 1st derivative and then finding the roots of the resulting cubic. Can I assume you recall the basic calculus needed to get the first derivative?

Finding the roots of anything above a 2nd order polynomial gets more challenging. There is a "closed form" solution for the roots of a cubic polynomial, but it passes through complex space on its way to getting the real roots (https://en.wikipedia.org/wiki/Cubic_polynomial ). If you decide to go this route, you will need to use the IM...() functions to perform the desired calculations in complex space (see brief discussion here and associated spreadsheet that illustrates the use of the IM...() functions http://www.excelforum.com/groups/mat...lex-roots.html ).

Personally, I prefer to use numerical root finding algorithms for these (https://en.wikipedia.org/wiki/Root-finding_algorithm ). The Newton Raphson method is fairly easy to program into a spreadsheet, especially if you are willing to allow circular references (see this discussion and example for NR algorithms: http://www.excelforum.com/tips-and-t...ml#post4229595 )

That should get you started. Let us know where you get stuck.