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

1. ## Scatter graph, obtaining a value of X for a given value of Y

Hi all, I have the attached graph and reading various threads I see I need the trend curve - which I have the polynomial best fit, and I can see the formula. What I cannot deduce is how this formula can be transposed to give me an X value for any given Y value?

Can you help?

Many Thanks
Steve

2. ## Re: 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&#39;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?

3. ## Re: Scatter graph, obtaining a value of X for a given value of Y

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

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

##### Users Browsing this Thread

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

#### 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