finding unknown from a line of best fit

Is there a way to use the formula and the line of best fit to plot a point on
the line using the y-value of an unknown in order to find the precise
location of the x-value?

Knowing y doesn't make it easy to find an unique x. Effectively, one
has invert the function y=f(x) to get x=g(y). Except in a few cases
such as a linear function, that will not lead to a unique solution. For
example, the simple y=x^2 yields 2 x values for any given positive y.

That said, you can get the equation of a line into a XL worksheet range.
Dave Braden has posted code that I enhanced to do just that. Search the
google.com archives of the XL NGs.

You can also directly get the equation of the line of best fit. See
Trendline coefficients
http://www.tushar-mehta.com/excel/ti...efficients.htm

Finally, for a readymade solution that gives you the result in an
interactive fashion see
Interactive Chart
http://www.tushar-
mehta.com/excel/software/interactive_chart_display/index.html


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <[email protected]>,
[email protected] says...
> Is there a way to use the formula and the line of best fit to plot a point on
> the line using the y-value of an unknown in order to find the precise
> location of the x-value?
>

For a linear or quadratic, inverting y=f(x) is simple algebra. Cubic or
quartic polynomials are not as simple, but
http://mathforum.org/dr.math/faq/faq...equations.html
should get you started.

There is no general algebraic solution to higher order polynomials, and
no requirement that there be a closed form inverse of a general function.

Given the functional relationship, you can numerically invert at a given
point using Solver.

If your "best fit" line is the smoothed line connecting the points
instead of a least squares trendline, then Excel appears to use cubic
splines for smoothing.

Jerry

Sara Beaver wrote:

> Is there a way to use the formula and the line of best fit to plot a point on
> the line using the y-value of an unknown in order to find the precise
> location of the x-value?