How do I get exact values from a graph in Excel?

[Carolyne]

Is it possible to get exact values from a graph that isn't one of your data
points? When I graph nonlinear variables is there some type of formula I can
type in to get me an exact x value for a specific y value. For example i have
x values of 0, 1, 2.5, 5, 10.... and y values of 100, 79, 55, 32, 12. If I
want to know what x equals when y=50 how do i go about doing that? I know
that I can't use the standard y=mx+b because my data is not linear... any
suggestions?? I've been trying to figure it out for a couple of weeks and its
starting to drive me crazy. Ive looked into a couple of other graphing
programs (Igor Pro, GraphPad Prism and Delta Graph 3 and they have been no
help either!)

Thank you!

 

[James Silverton]

Carolyne wrote on Tue, 11 Jul 2006 13:05:02 -0700:

C> Is it possible to get exact values from a graph that isn't
C> one of your data points? When I graph nonlinear variables is
C> there some type of formula I can type in to get me an exact
C> x value for a specific y value. For example i have x values
C> of 0, 1, 2.5, 5, 10.... and y values of 100, 79, 55, 32, 12.
C> If I want to know what x equals when y=50 how do i go about
C> doing that? I know that I can't use the standard y=mx+b
C> because my data is not linear... any suggestions?? I've been
C> trying to figure it out for a couple of weeks and its
C> starting to drive me crazy. Ive looked into a couple of
C> other graphing programs (Igor Pro, GraphPad Prism and Delta
C> Graph 3 and they have been no help either!)

Despite its name, FORECAST can be used to get points internal to
a graph.

James Silverton.

 

[Tushar Mehta]

If you are willing to use a regression trendline, then contrary to what the
documentation might lead you to believe, you are not restricted to a linear
equation. For more see the 'Regression with polynomials' section in
Trendline Coefficients and Regression Analysis
http://www.tushar-mehta.com/excel/tips/trendline_coefficients.htm

If you want to use a piecewise linear interpolation see
Interactive Chart
http://www.tushar-
mehta.com/excel/software/interactive_chart_display/index.html

and

Interpolation
http://www.tushar-mehta.com/excel/newsgroups/interpolation/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jerry W. Lewis]

If your data is noisy, then fitting a polynomial (as others suggested) can
smooth it before you average. However, if the data is not noisy and not from
a low order polynomial, you should be aware that a least squares fit need not
pass through any observed points.

If you really want the value that the chart displays, then the chart
smoother appears to fit Bezier curves
http://www.xlrotor.com/Smooth_curve_bezier_example_file.zip
which in most instances is not greatly different than cubic splines
http://groups.google.com/group/microsoft.public.excel.programming/msg...
(local polynomial fits that do pass through the data points, as opposed to a
global polynomial fit that may not).

Jerry