Thread
:
getting an x-value from plotting a y-value
View Single Post
#
8
Posted to microsoft.public.excel.misc
Sumit Kumar[_2_]
external usenet poster
Posts: 3
getting an x-value from plotting a y-value
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
Reply With Quote
Sumit Kumar[_2_]
View Public Profile
Find all posts by Sumit Kumar[_2_]