formula to identify exact point where trendline intersects data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column of data for which I have created a scatter chart and then
sorted in descending order after which I added a trendline. I can hover my
cursor to see the data point at which the trendline intersects my line of
data but.....

I need a formula that gives me the exact data point at which the trendline
interesects the data.

thank you in advance! Jane
 
Hi Jane,

If you format the trendline to display its equation on the chart, that's the
formula you need.

Cheers
 
hi there,
this is the formula that came up - how do I apply it to my data?
y = -0.0001x + 0.1279

macropod said:
Hi Jane,

If you format the trendline to display its equation on the chart, that's the
formula you need.

Cheers

--
macropod
[MVP - Microsoft Word]


Jane said:
I have a column of data for which I have created a scatter chart and then
sorted in descending order after which I added a trendline. I can hover my
cursor to see the data point at which the trendline intersects my line of
data but.....

I need a formula that gives me the exact data point at which the trendline
interesects the data.

thank you in advance! Jane
Click to expand...
Click to expand...
 
or is this my answer? R2 = 0.9003 so the exact point that intersects is
90.03% (I need to express my result as a %)
thansk! jane

macropod said:
Hi Jane,

If you format the trendline to display its equation on the chart, that's the
formula you need.

Cheers

--
macropod
[MVP - Microsoft Word]


Jane said:
I have a column of data for which I have created a scatter chart and then
sorted in descending order after which I added a trendline. I can hover my
cursor to see the data point at which the trendline intersects my line of
data but.....

I need a formula that gives me the exact data point at which the trendline
interesects the data.

thank you in advance! Jane
Click to expand...
Click to expand...
 
Jane,

It is possible that NONE of your data might actually fit the line.
That's the idea with trendlines, we have a discrete set of (x,y) pairs
and we try to find a continuous function that best fits them.

One thing you can do is use a column next to your dependent variable
and plot the function that you get from the trendline using your
independent variable column in place of x (e.g. =0.01*A2+7890)

Once you copy this you can see which values of x (if any) match the
value the trend function produces.

HTH
Kostis Vezerides

Hi Jane,

If you format the trendline to display its equation on the chart, that's the
formula you need.

Cheers

--
macropod
[MVP - Microsoft Word]


Jane said:
I have a column of data for which I have created a scatter chart and then
sorted in descending order after which I added a trendline. I can hover my
cursor to see the data point at which the trendline intersects my line of
data but.....

I need a formula that gives me the exact data point at which the trendline
interesects the data.

thank you in advance! Jane
Click to expand...
Click to expand...
 
Hi Jane,

What the formula tells you is the relationship between your 'x' and 'y'
values. As displayed (y = -0.0001x + 0.1279), it shows how to solve for 'y'.
To solve for x you'd use: x =1279 -10000y.

Without knowing your data I can't tell you the which of your data points
exactly matches the intersection - and there may be none, one or more than
one.

To know for sure, you'll have to run the formula against each of your 'x' or
'y' values and compare the results. Say, for example, you have 20 'x' values
numbered 1-20 in cells A1:A20, and your 'y' values in cells B1:B20. if you
put the formula '=-0.0001*A1+0.1279' in another cell (eg D1), and copy down
19 rows, you'll have 20 rows of corresponding theoretical 'y' values. Now
it's just a matter of seeing whether any of these matches an actual 'y'
value on the same row.

Cheers

--
macropod
[MVP - Microsoft Word]


Jane said:
hi there,
this is the formula that came up - how do I apply it to my data?
y = -0.0001x + 0.1279

macropod said:
Hi Jane,

If you format the trendline to display its equation on the chart, that's the
formula you need.

Cheers

--
macropod
[MVP - Microsoft Word]


Jane said:
I have a column of data for which I have created a scatter chart and then
sorted in descending order after which I added a trendline. I can
Click to expand...
hover
my
cursor to see the data point at which the trendline intersects my line of
data but.....

I need a formula that gives me the exact data point at which the trendline
interesects the data.

thank you in advance! Jane
Click to expand...
Click to expand...
Click to expand...
 
Back
Top