Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Returning values from a graph plot?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a XY (scatter) chart that is logarithmic on the X axis and linear on the Y axis. I need to have Excel report the X value for the intersection of a graphed curve with a specific Y value. I know this can be done with a trendline, but I need the values for the actual plotted curve. The trendline types do not adhere closely enough to the plotted curve to be useful. Any ideas?

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi TimB,

    Unless you have a point on the curve that always happens to be right at the Y value you want, you will need to use some sort of interpolation. What type of interpolation you select should be based on several factors, including the spacing between points, the "curvature" of the curve, etc. If the "curve" is pretty close to a straight line on your log-linear plot, then it represents a logarithmic relationship between x and y, and logarithmic interpolation should give you a very accurate answer. Logarithmic interpolation is just like linear interpolation except that you use the log (can be any base) of the y values, and the interpolation yields an x,y pair where the y value is the log of the actual y value, so you must take the antilog to get it.

    It would not be difficult to write a macro that would examine a chart data series and find the points that bound the y-value of interest, and then perform log interpolation (or other types of interpolation) to find the corresponding x-value. This probably could also be done as a UDF so that as the data changed the new x-value would be re-computed automatically. If you need any pointers, let me know.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •