getting an x-value from plotting a y-value

[Moni]

I have 2 columns of data eg.

X Y I want to find an unknown x-value from a y-value of
0.277.
How do i use excel to plot this on a scattergraph?
0 0 Please help!
0.5 0.128
1.0 0.260
1.5 0.389
2.0 0.523
2.5 0.637

 

[Gary''s Student]

Since y=.277 we know that x is somewhere between 1.0 and 1.5

Read about the FORECAST() function in Excel Help. It does a great job of
interpolating between two points.

 

[MartinW]

Hi Moni,

First put your x data in A1 to A6 and your y data in B1 to B6.
Then highlight your data and go to insert>chart.
In the chart wizard select xy scatter and click next.
In the next window check columns and click next
This next window is only for formatting purposes
and can be bypassed by clicking finish.

Now in your chart right click on one of the data points and
select Add Trendline. Select linear, click on the options tab
and check 'display equation on chart' then OK.

You will now have a line of best fit through your data
and an equation for that line in the form of Y=Mx+C
where M is the gradient (SLOPE function)
and C is the Y-intercept (INTERCEPT function)

Now put this formula in E1
=(D1-(INTERCEPT(B1:B6,A1:A6)))/SLOPE(B1:B6,A1:A6)

Now you can plug any y value into D1 and E1 will return
the x value.

Note that this is not exact as your data doesn't fit
a linear regression exactly, however in your example
it is very close.

HTH
Martin

 

[Moni]

Thank you Martin for your clear instructions. It was very helpful!

 

[Moni]

I tried Martins way which gave me the correct answer, then i read about
forecast and gave that a go too. Thankyou, now i know two ways of doing it.

 

[Sumit Kumar]

Hello Martin,
I have the same query as moni but i need to implement it in my program. For
that i explored the forecast method in excel and could not figure out the
formula. X(Bar) & Y(Bar) signify what ?

Thanks in advance

 

[David Biddulph]

See your other post.
--
David Biddulph

Hello Martin,
I have the same query as moni but i need to implement it in my program.
For
that i explored the forecast method in excel and could not figure out the
formula. X(Bar) & Y(Bar) signify what ?

Thanks in advance

 

[Sumit Kumar]

Hello David,

Please see the below formula which i took from excel help: -

The equation for FORECAST is a+bx, where:
a=y(bar) - bx(bar)
and:
b=Summation of{(x-x(bar)) (y-y(bar))} / Summation of (x-x(bar)) power2

and where x and y are the sample means AVERAGE(known_x's) and AVERAGE(known
y's).

So if X & Y are the average values then i want to know what x(bar)
& Y (bar) stand for ?

Please see the excel formula for FORECAST method incase the above formula is
not clearly stated.

 

[Bernard Liengme]

X(bar) is the average of the x-values.
You need to consult a book (or web site) on curve fitting or least-squares
fit.
best wishes

 

[David Biddulph]

I believe that in that bit of Excel help where it says:
"and where x and y are the sample means AVERAGE(known_x's) and AVERAGE(known
y's)."
it should say:
"and where x(bar) and y(bar) are the sample means AVERAGE(known_x's) and
AVERAGE(known y's)."
as I assume that the bar signs have been lost in the copying from the
formula.