Returning values from a graph plot?

TimB

New Member
Joined
Mar 25, 2002
Messages
8
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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top