How do I find an unknown value on a line made from known data?

B

brendas182

I'm trying to find an unknown data point (X) by using a set of standards (two
sets of data, an X and Y)that have been made into a line graph. Usually I do
this manually by graphing the line and printing out a large version and just
giving a best estimate to where the point would be. The closest thing I can
find that functions in this way is a trendline, but the point found needs to
actually be on the line itself, not the trendline. I was wondering if any of
you know how to do this using excel because I can't seem to figure it out.
Thank you!
 
B

bj

It sounds as though you need to interpolate between the two data points on
either side of your unkknown point.
Are you looking for an X to go with a known Y or a Y to go with a known X
 
J

Jerry W. Lewis

Your question is not entirely clear. If you are talking about a
straight line,
y = m*x + b
implies that
x = (y-b)/m

For the best fit (least squares) line, m and b can be computed by the
SLOPE() and INTERCEPT() functions. For the line connecting two
particular points, you could use SLOPE() and INTERCEPT with just those
two points, or calculate directly from the definition
slope = (y2-y1)/(x2-x1)

If by "line graph" you mean the Excel "Line" chart type, you should be
aware that the "Line" chart assumes that x values (if any) are category
labels, not number therefore it equally spaces the plotted points even
if inappropriate for the supplied x-values. If you fit a trendline on a
"Line" graph, it will base that "trend" on associating the supplied
y-values with hypothetical x-values of 1,2,3,4,...

Jerry
 
Top