problem with chart values!

[deep_dish]

i have kind of problem with chart values! I created a chart with some data, for y-axis something like 10,20,30.... and each has coresponding value in x-axis. Now i used scatter with smooth lines, and excel made a line that aproksimates my graph. now i need to know which value of x-axis coresponds to, for example, value 26 of y-axis!??

can somebody help me?
thanks

[deep_dish]

p.s. and of course how could i mark those points on a graph

thanks again

[MrShorty]

While the chart is nice for visualizing things, I doubt the chart itself is even going to figure into the solution.

What kind of solution do you need/want? A LOOKUP solution where 26 is present in the Y column and you need to return the corresponding x value? An interpolation solution where 26 isn't present, but you want to use the values for y=20 and 30 and the corresponding x values to estimate what x would be at y=26? Or a curve fitting solution where you use all of the values to estimate a best fit curve, then solve that equation for x at y=26?

[Bryan Hessey]

i have kind of problem with chart values! I created a chart with some data, for y-axis something like 10,20,30.... and each has coresponding value in x-axis. Now i used scatter with smooth lines, and excel made a line that aproksimates my graph. now i need to know which value of x-axis coresponds to, for example, value 26 of y-axis!??

can somebody help me?
thanks

Have you included Lables for X and Y axis? or were you after 'Tick Mark Labels' ? or perhaps a line from the y-axis to the x-axis as shown inhttp://www.excelforum.com/attachment.php?attachmentid=5848&d=1161781626 in the post at http://www.excelforum.com/showthread.php?t=578770

- can you post your chart, it might help.

---

[deep_dish]

Mr shorty, it would be better of course if it could use all values and do a better estimating of a value, but i also could use the values for y=20 and 30 and the corresponding x values to estimate what x would be at y=26! which would be less precise but it would be ok!
How can i do either of those two????

[MrShorty]

The mechanics of a a curve-fitting solution are pretty straightforward. My preference is to use the LINEST function. The hardest part of curve-fitting sometimes is deciding on the form of the equation. Do you want a polynomial, or an exponential equation, or something else? Especially with polynomials, you have to be careful about using too many parameters. Do you want to fit y as a function of x (y=f(x)) or the other way around (x=f(y))? Recognize that sometimes you'll select an equation y=f(x) that cannot be solved explicitly for x, in which case you'll need numerical methods (Solver and Goal Seek can be good utilities) to get x from y.

[deep_dish]

ok, thanks man, ill see what i can do

[deep_dish]

Ok, here's kind of a thing i have to do! This line is formed from some data, for example (20;0,6), (50;2), (100;7).... (this is x and y values)....and now for y value of 170 i need a coresponding value on x axis!!! How can i calculate this (aproximately)???

[MrShorty]

What part specifically are you having trouble with?

If you still want a curve fitting solution, it looks to me like a simple 2nd order polynomial should do the job, but you'd have to try it and see. H=A+BQ+CQ^2. Once you've got the parameters, then put 170 in for Q and you'll get H (Looks like it should be ~22).

If it's easier for you, an interpolation solution looks like it should work just fine, depending on how closely spaced your data points are. The hardest part of using linear interpolation in Excel is locating the interval containing the unknown. Once the interval is located, the equation is simply (Q2-Q1)/(H2-H1)=(Q3-Q1)/(H3-H1) and solve for the appropriate unknown.