+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    01-09-2014
    Location
    Oakham, England
    MS-Off Ver
    Excel 2010
    Posts
    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
    Attached Files Attached Files

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

    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'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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,903

    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

    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
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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. Graph - Horizontal line in scatter graph & intercept
    By natkemon in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-17-2018, 11:50 AM
  2. [SOLVED] Bar graph and scatter graph issue (all in one)
    By jw01 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 05-16-2017, 09:21 AM
  3. Replies: 31
    Last Post: 02-06-2016, 06:09 AM
  4. Combining Stacked Bar Graph with Scatter Graph
    By cunningj in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-08-2014, 04:29 AM
  5. Scatter Graph to show data along side scatter points
    By jonathan.haynes in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-15-2013, 08:59 AM
  6. Scatter Graph?
    By jrakowski02 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-05-2006, 10:32 PM
  7. [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