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

--
macropod
[MVP - Microsoft Word]


"Jane" wrote in message
...
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 there,
this is the formula that came up - how do I apply it to my data?
y = -0.0001x + 0.1279

"macropod" wrote:

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

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" wrote:

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

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


macropod wrote:
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" wrote in message
...
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,

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" wrote in message
...
hi there,
this is the formula that came up - how do I apply it to my data?
y = -0.0001x + 0.1279

"macropod" wrote:

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