Finding graph X,Y coordinates using Mouse Cursor

I've attached a photo of problem.

I'm trying to find the exact x-axis coordinate, when this line crosses the various y-axis points.

For example, in the picture the line hits a Y-axis value of 4 at approximately X=0.235, this is done by eye.

Is there an easier way to query where the line crosses the these points, for example using the Mouse Cursor?

Capture.PNG

Welcome to the Forum!

I don't know of a way to do this with the cursor but it could be done by interpolation using the source data. I'm assuming that the graph is in Excel, generated by data in Excel. The accuracy of the interpolation would depend on how close together the data points are, but just by eyeballing it, it looks like linear interpolation would be a good approach here.

You can get fancier by using macros to do the interpolation based on some Y value you select, so it could be interactive.

I could provide a more concrete answer if you can attach your file. Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

Hi 6StringJazzer and thank you!

I've attached a file. It's not the original as I've some sensitive data in their which I can't give out... (or men in suits might come to my door and shoot me ).

However, this file should explain my problem. Thank you for your help so far.

Your file says:

I need to find the X-axis coordinates where the trendline (red) meets the Z axis

That is quite different than the question you asked above. Your first question didn't indicate that you were showing a trendline, and you seemed to be looking for the y value at an arbitrary x value.

To answer the question you ask in your file, the trendline meets the Y axis (labelled Z on your chart) where x=0. That means you just need the constant value from the trendline equation. The quickest way to do this is look at the equation. Right-click on the trendline, select Format Trendline, then check the box for Display Equation on Chart. For Plot 1 that is:

y = -4207.1x^6 + 12509x^5 - 14934x^4 + 9110.8x^3 - 2979.8x^2 + 504.89^x - 32.291

So the trendline crosses the Y axis at -32.291.

I will agree, you could be clearer about what exactly you want.

As 6stringjazzer notes, to obtain the z value at x=0, simply put 0 in for x in the trendline and compute y (which is just the constant term in the polynomial). For other values of x, simply put that value for x in and compute z.

From your original post, I first thought you were looking to give the equation z, then find x. For a 6th order polynomial, this is not possible algebraically, but one can easily use numerical algorithms to solve for x at a given z. Usually the easiest approach is to use Excel's built in Solver/Goal Seek utilities.

In all of these scenarios, the main "difficulty" to your problem is that you have used the chart trendline feature to perform the regression, so you need to transfer the equations coefficients into the spreadsheet. If this is a one time or rare task, then it is probably easiest to simply hand enter the coefficients into the spreadsheet. If this is something you will need to do frequently, I recommend that you become familiar with using the LINEST() function to perform polynomial regressions. The help file (https://support.office.com/en-us/art...a-fa7abf772b6d ) includes an example for a 3rd order polynomial. Extending that to a 6th order polynomial should be straightforward.