Scatter graph, obtaining a value of X for a given value of Y

I am old enough to recall the day when it was believed that there were no "closed form" solutions for cubic or higher polynomials. I understand that people have derived closed form solutions for cubics and quartics (see Wikipedia here for a derivation and discussion of a solution attributed to a Ferrari: https://en.wikipedia.org/wiki/Quarti...ri's_lines ).

The programming question you must answer before we talk about how to program this in Excel is deciding if you want to try to program that kind of mess into Excel, or if you would prefer to program a numeric algorithm mess (Newton-Raphson or similar algorithm) into Excel? For one time or rare solutions, manually using Excel's built in Solver or Goal seek is almost certainly the easiest to program. Solver/Goal Seek can be automated using VBA, or you can program your own root finding algorithm (see my tutorial here: https://www.excelforum.com/tips-and-...ind-roots.html ). I think many programmers reserve the closed form solution for programming scenarios where computation speed is critical.

In any of these programming scenarios, I expect it will be preferable to generate the coefficients of the polynomial directly in the spreadsheet using the LINEST() function rather than the chart's trendline feature. See the help file (https://support.microsoft.com/en-us/...rs=en-us&ad=us ) for a cubic polynomial example using the LINEST() function that should be easy to expand to a quartic.

How would you like to proceed? How can we help?

Hi,

If you format the trend line formula to scientific notation rather than general, you'll then see

y = -3.15E-14x4 + 7.42E-10x3 - 6.30E-06x2 + 2.40E-02x - 3.40E-01

instead of
y = -3E-14x4 + 7E-10x3 - 6E-06x2 + 0.024x - 0.3404

Now if you use the more precise scientific numbers the y value for any x value in say C31 is

=-0.0000000000000315*C31^4+0.000000000742*C31^3-0.0000063*C31^2+0.024*C31-0.3404

I find it helps to break the elements down as in the attached, see C31:I31

When entering those numbers, rather than entering all the zeros manually just enter stuff like -3.15E-14*c31^4, ...etc