Returning values from a graph plot?

[TimB]

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?

[Damon Ostrander]

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.