Excel Graph Making Asshattery.

Johannen

Right!

I've got a graph in Excel, it is a line graph and it has an x axis and a y axis of course. Now, I want to draw a line across from a place on the y axis to the line that makes up my graph, and then down to the corresponding point on the x axis. When I do this I want to know what the value from the x axis that I have drawn to is.

Does anyone know how to do this? Is it possible to do on Excel?

It's a bit of a bitch to be true, mainly due to me needing to have done this by tomorrow.

Any help would be muchos muchos appreciated.

whuppins

You want to draw a line (ok, two lines) on the graph, then have Excel return the point referenced by the intersection?

Or, you want to designate a point along your curve and have Excel plot the lines?

If the first one, you can't do that.

If the second, I'm not sure you can do that either -- most of my Excel knowledge is with formula logic and VBA -- but I'll play around and see if there's an option for that. I wouldn't hold your breath, though... Excel's charts aren't particularly rich.

Ramius

With What precision do you need to know the value?

I mean, I was able to create something like this:



But I'm not at all clear that this helps you.

Orogogus

Excel isn't really ideal for this, but you can interpolate on a graph by doing the following:

1. Create a trendline on your graph:
- Left click on your data points so that the entire series is highlighted
- Right click on the series and choose "Add Trendline"
- Choose the appropriate curve under the Type tab
- Go to the Options tab, and check off "Display equation on chart"

2. Plug the equation back into your spreadsheet so that you can plug in an x value and get back a y value (or vice versa). So if the equation that shows up on the graph is y = 2.0181x - 0.7636, you could type the equation =(A1*2.0181)-0.7636 into a cell.

whuppins

Okay, this isn't going to solve your problem, but I think that's because Excel simply can't do what you need it to. Here's as close as I got:

First of all, this info pertains to an XY scatter chart fitted with a line. This is different from a line chart, which will only plot a one-dimensional array of Y values. Because of this, you can't get the effect you want on both axes with a line chart. Depending on the nature of your data, you may be able to re-do your chart in XY scatter form. In fact, this should be very easy for most kinds of data.

Anyway, if you have an existing XY scatter chart, right-click on one of your data points and pick Format Data Series. What you'll be doing is using error bars to fake your little reference lines. There are two drawbacks in your situation:

a.) The bars still won't print values at the places where they intersect your axes. You can, however, enable the values next to the points themselves by checking "X Value" and "Y Value" in the Data Labels tab.

b.) The bars will be plotted for all points, not just a single point of your choosing. As far as I know, there's no way around this. Sorry.

Under the X Error Bars tab, select "Minus" as the display type and "Percentage" from the Error Amount box. Specify 100% and repeat this under the Y Error Bars tab. At this point your chart should be equivalent to the below example.



I know this wasn't what you were looking for, but it may be as close as you can get. If you have any leeway in terms of what the chart needs to look like or how the data is to be plotted, let me know and I may be able to come up with something more appropriate.

Johannen

Yeah, thanks for the help guys, I had a feeling Excel was going to fuck me over on this, and I pretty much need to be able to choose the Y value and have it draw out a line and give me back the X value. I can kind of do it with the equation like orogogus said but it doesn't draw me a line and isn't really very accurate (which I need it to be).

Thanks for the help anyway, i'm gonna be up all night with these essays and all. Thanks guys.

I'll as for this to be locked now because I can't see it being possible in excel.

Orogogus

If this is for a specific presentation (as opposed to something you will need to do over and over again) by tomorrow, the ugly hackjob way to do it would be to:

1. Do the trendline equation thing, but just between the two points around the target line segment. You would probably have to create a chart with just those two data points in it for this purpose, and then throw it away after you have your equation.

2. Open Excel's drawing tools and just draw the x- and y-lines in where they should be. Don't move or resize the graph after you do this, because the drawing objects won't move or scale with it. You can draw a text box in and put in the value, too, if desired.