Return the location of data point on a chart

Using vba for excel, how do I return the location of a specific data point in
Excel. For example, say I have 3 data points on a line graph. I want to
know the coordinates of the 2nd point. I need something that will tell me it
is located at coordinates (248.25, 58.5, 248.25, 205.5).

thanks!

ND -

You can use algebra to determine the point's coordinates based on its values, on the
axis scales, and the coordinates of the chart's plot inside area. In this post, I
show how to draw a shape that simulates an area chart, except it fills in the area
to the left of an XY scatter series:

http://groups-beta.google.com/group/...40c814427f3afa

This one explains how to draw a filled shape connecting the points in an XY series:

http://groups-beta.google.com/group/...b7c8736b5852c3

What will you use these coordinates for?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

nd wrote:

> Using vba for excel, how do I return the location of a specific data point in
> Excel. For example, say I have 3 data points on a line graph. I want to
> know the coordinates of the 2nd point. I need something that will tell me it
> is located at coordinates (248.25, 58.5, 248.25, 205.5).
>
> thanks!

Thank you sir! I have made some good progress on my project by looking at
the examples that are posted in your links. What I am trying to do is this:

I have a linegraph that shows frequencies over time. When there is a phase
change in my experiment, I break the line between two data points (I can do
that). I then insert a drawing of a line by saying something like
"ActiveChart.Shapes.AddLine(133.5, 51#, 133.5, 207#).Select", thus indicating
a phase change. The problem is that when I add new data points, the
linegraph scrunches together more, and the line does not move
correspondingly. I need my phase change line to move when the data points
move. I was thinking that if there was some command in vba like
mySrs.datapoints(2).location that would return (133.5, 51#, 133.5, 207#).
Then I could tell it to move my phase line accordingly.

The examples you gave are a good start for me. I'm a rather elementary
programmer, so I will probably have to spend some time tinkering around with
this until it works. If there is something super-easy, I would appreciate
any additional tips!

Thanks for your help!
Neill


"Jon Peltier" wrote:

> ND -
>
> You can use algebra to determine the point's coordinates based on its values, on the
> axis scales, and the coordinates of the chart's plot inside area. In this post, I
> show how to draw a shape that simulates an area chart, except it fills in the area
> to the left of an XY scatter series:
>
> http://groups-beta.google.com/group/...40c814427f3afa
>
> This one explains how to draw a filled shape connecting the points in an XY series:
>
> http://groups-beta.google.com/group/...b7c8736b5852c3
>
> What will you use these coordinates for?
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> nd wrote:
>
> > Using vba for excel, how do I return the location of a specific data point in
> > Excel. For example, say I have 3 data points on a line graph. I want to
> > know the coordinates of the 2nd point. I need something that will tell me it
> > is located at coordinates (248.25, 58.5, 248.25, 205.5).
> >
> > thanks!
>
>

Could you include a second line series based on similar data. Perhaps with
no markers, make unwanted points invisible as & when, colour formatted to
suit.

Just a thought, Jon may well have a better suggestion.

Regards,
Peter T

"nd" <[email protected]> wrote in message
news:[email protected]...
> Thank you sir! I have made some good progress on my project by looking at
> the examples that are posted in your links. What I am trying to do is
this:
>
> I have a linegraph that shows frequencies over time. When there is a
phase
> change in my experiment, I break the line between two data points (I can
do
> that). I then insert a drawing of a line by saying something like
> "ActiveChart.Shapes.AddLine(133.5, 51#, 133.5, 207#).Select", thus
indicating
> a phase change. The problem is that when I add new data points, the
> linegraph scrunches together more, and the line does not move
> correspondingly. I need my phase change line to move when the data points
> move. I was thinking that if there was some command in vba like
> mySrs.datapoints(2).location that would return (133.5, 51#, 133.5, 207#).
> Then I could tell it to move my phase line accordingly.
>
> The examples you gave are a good start for me. I'm a rather elementary
> programmer, so I will probably have to spend some time tinkering around
with
> this until it works. If there is something super-easy, I would appreciate
> any additional tips!
>
> Thanks for your help!
> Neill
>
>
> "Jon Peltier" wrote:
>
> > ND -
> >
> > You can use algebra to determine the point's coordinates based on its
values, on the
> > axis scales, and the coordinates of the chart's plot inside area. In
this post, I
> > show how to draw a shape that simulates an area chart, except it fills
in the area
> > to the left of an XY scatter series:
> >
> >
http://groups-beta.google.com/group/...rting/browse_f
rm/thread/9e971e449deed969/8940c814427f3afa
> >
> > This one explains how to draw a filled shape connecting the points in an
XY series:
> >
> >
http://groups-beta.google.com/group/...rting/browse_f
rm/thread/2058c388294e03a/99b7c8736b5852c3
> >
> > What will you use these coordinates for?
> >
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Peltier Technical Services
> > Tutorials and Custom Solutions
> > http://PeltierTech.com/
> > _______
> >
> > nd wrote:
> >
> > > Using vba for excel, how do I return the location of a specific data
point in
> > > Excel. For example, say I have 3 data points on a line graph. I want
to
> > > know the coordinates of the 2nd point. I need something that will
tell me it
> > > is located at coordinates (248.25, 58.5, 248.25, 205.5).
> > >
> > > thanks!
> >
> >

Could you include a second line series based on similar data. Perhaps with
no markers, make unwanted points invisible as & when, colour formatted to
suit.

Just a thought, Jon may well have a better suggestion.

Regards,
Peter T

"nd" <[email protected]> wrote in message
news:[email protected]...
> Thank you sir! I have made some good progress on my project by looking at
> the examples that are posted in your links. What I am trying to do is
this:
>
> I have a linegraph that shows frequencies over time. When there is a
phase
> change in my experiment, I break the line between two data points (I can
do
> that). I then insert a drawing of a line by saying something like
> "ActiveChart.Shapes.AddLine(133.5, 51#, 133.5, 207#).Select", thus
indicating
> a phase change. The problem is that when I add new data points, the
> linegraph scrunches together more, and the line does not move
> correspondingly. I need my phase change line to move when the data points
> move. I was thinking that if there was some command in vba like
> mySrs.datapoints(2).location that would return (133.5, 51#, 133.5, 207#).
> Then I could tell it to move my phase line accordingly.
>
> The examples you gave are a good start for me. I'm a rather elementary
> programmer, so I will probably have to spend some time tinkering around
with
> this until it works. If there is something super-easy, I would appreciate
> any additional tips!
>
> Thanks for your help!
> Neill
>
>
> "Jon Peltier" wrote:
>
> > ND -
> >
> > You can use algebra to determine the point's coordinates based on its
values, on the
> > axis scales, and the coordinates of the chart's plot inside area. In
this post, I
> > show how to draw a shape that simulates an area chart, except it fills
in the area
> > to the left of an XY scatter series:
> >
> >
http://groups-beta.google.com/group/...rting/browse_f
rm/thread/9e971e449deed969/8940c814427f3afa
> >
> > This one explains how to draw a filled shape connecting the points in an
XY series:
> >
> >
http://groups-beta.google.com/group/...rting/browse_f
rm/thread/2058c388294e03a/99b7c8736b5852c3
> >
> > What will you use these coordinates for?
> >
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Peltier Technical Services
> > Tutorials and Custom Solutions
> > http://PeltierTech.com/
> > _______
> >
> > nd wrote:
> >
> > > Using vba for excel, how do I return the location of a specific data
point in
> > > Excel. For example, say I have 3 data points on a line graph. I want
to
> > > know the coordinates of the 2nd point. I need something that will
tell me it
> > > is located at coordinates (248.25, 58.5, 248.25, 205.5).
> > >
> > > thanks!
> >
> >

Peter -

Actually, I was going to suggest exactly that. A new series is tied to the X and Y
values of the chart axis coordinates, not of the chart object, so it will move when
the axis is rescaled.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Peter T wrote:

> Could you include a second line series based on similar data. Perhaps with
> no markers, make unwanted points invisible as & when, colour formatted to
> suit.
>
> Just a thought, Jon may well have a better suggestion.
>
> Regards,
> Peter T
>
> "nd" <[email protected]> wrote in message
> news:[email protected]...
>
>>Thank you sir! I have made some good progress on my project by looking at
>>the examples that are posted in your links. What I am trying to do is
>
> this:
>
>>I have a linegraph that shows frequencies over time. When there is a
>
> phase
>
>>change in my experiment, I break the line between two data points (I can
>
> do
>
>>that). I then insert a drawing of a line by saying something like
>>"ActiveChart.Shapes.AddLine(133.5, 51#, 133.5, 207#).Select", thus
>
> indicating
>
>>a phase change. The problem is that when I add new data points, the
>>linegraph scrunches together more, and the line does not move
>>correspondingly. I need my phase change line to move when the data points
>>move. I was thinking that if there was some command in vba like
>>mySrs.datapoints(2).location that would return (133.5, 51#, 133.5, 207#).
>>Then I could tell it to move my phase line accordingly.
>>
>>The examples you gave are a good start for me. I'm a rather elementary
>>programmer, so I will probably have to spend some time tinkering around
>
> with
>
>>this until it works. If there is something super-easy, I would appreciate
>>any additional tips!
>>
>>Thanks for your help!
>>Neill
>>
>>
>>"Jon Peltier" wrote:
>>
>>
>>>ND -
>>>
>>>You can use algebra to determine the point's coordinates based on its
>
> values, on the
>
>>>axis scales, and the coordinates of the chart's plot inside area. In
>
> this post, I
>
>>>show how to draw a shape that simulates an area chart, except it fills
>
> in the area
>
>>>to the left of an XY scatter series:
>>>
>>>
>
> http://groups-beta.google.com/group/...rting/browse_f
> rm/thread/9e971e449deed969/8940c814427f3afa
>
>>>This one explains how to draw a filled shape connecting the points in an
>
> XY series:
>
>>>
> http://groups-beta.google.com/group/...rting/browse_f
> rm/thread/2058c388294e03a/99b7c8736b5852c3
>
>>>What will you use these coordinates for?
>>>
>>>- Jon
>>>-------
>>>Jon Peltier, Microsoft Excel MVP
>>>Peltier Technical Services
>>>Tutorials and Custom Solutions
>>>http://PeltierTech.com/
>>>_______
>>>
>>>nd wrote:
>>>
>>>
>>>>Using vba for excel, how do I return the location of a specific data
>
> point in
>
>>>>Excel. For example, say I have 3 data points on a line graph. I want
>
> to
>
>>>>know the coordinates of the 2nd point. I need something that will
>
> tell me it
>
>>>>is located at coordinates (248.25, 58.5, 248.25, 205.5).
>>>>
>>>>thanks!
>>>
>>>
>
>
>

After discovering this thread I was able to add velocity vectors to an animated projectile motion chart. A small modification I learned that needed to be made was that the data point coordinates required a small correction if the chart is embedded in a worksheet. In my case I needed to add 4 to each calculated coordinate. What I don't know is whether there is a chart property that determines this offset.

George

heyy i need help in xls..i drew charts , now i need a macro to find me the y-coordinate of a point when i enter the x-coordinate..thank you