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

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.
--
Gary's Student


"Moni" wrote:

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

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 insertchart.
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

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


"MartinW" wrote:

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 insertchart.
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

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.

"Gary''s Student" wrote:

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.
--
Gary's Student


"Moni" wrote:

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

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

"Moni" wrote:

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


"MartinW" wrote:

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 insertchart.
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

See your other post.
--
David Biddulph

"Sumit Kumar" <Sumit wrote in message
...
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

"Moni" wrote:

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


"MartinW" wrote:

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 insertchart.
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

Hello David,

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

The equation for FORECAST is a+bx, whe
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.



"David Biddulph" wrote:

See your other post.
--
David Biddulph

"Sumit Kumar" <Sumit wrote in message
...
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

"Moni" wrote:

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


"MartinW" wrote:

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 insertchart.
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

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Sumit Kumar" <Sumit wrote in message
...
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

"Moni" wrote:

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


"MartinW" wrote:

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 insertchart.
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

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.
--
David Biddulph

"Sumit Kumar" wrote in message
...
Hello David,

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

The equation for FORECAST is a+bx, whe
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.



"David Biddulph" wrote:

See your other post.
--
David Biddulph

"Sumit Kumar" <Sumit wrote in message
...
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

"Moni" wrote:

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


"MartinW" wrote:

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 insertchart.
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