Thread: Moveable Vertical Line

I am trying to create a chart with two vertical lines that are moveable by the user clicking and dragging them to a spot on the chart. One line is for the starting x value and one is for the ending x value inside the given range.

Once I have the start and end lines placed I would like to do an average of all the values that fall in between them. And also find the min/max values.

What I am having trouble doing is creating a vertical line that a user can click on and move to whatever x value they want. I can create the line but how do I assign it's position to a particular x value???

Any ideas would be appreciated.

Blaine

Hi Blaine,

You can (sort of) do this in a scatter chart. I don't know how much
experience you have with charts, if this explanation is too sketchy, please
ask again.

1) Build a scatter chart with the original series and a range for the
x-Axis.
2) Copy the first two cells of the x-Axis range to a new range
3) Add two y-Values to the right of the new x-Axis cells, make the values
equal to the minimum of the original series y-Values.
4) Add two new series based on the two new cells - these series will have a
single point.
5) Format the two new series to have 100% y-Error bars.
6) You may have to explicitly set the y-Scale minimum / maximum values

Now you should have two vertical lines (the error bars). Users can move
these by clicking on the point which can be on the x-Axis or just above it.
The x-Values change on the worksheet when users move one of these points, so
it should be easy to do calculations with them.

Ed Ferrero
http://edferrero.m6.net/

>
> I am trying to create a chart with two vertical lines that are moveable
> by the user clicking and dragging them to a spot on the chart. One line
> is for the starting x value and one is for the ending x value inside the
> given range.
>
> Once I have the start and end lines placed I would like to do an
> average of all the values that fall in between them. And also find the
> min/max values.
>
> What I am having trouble doing is creating a vertical line that a user
> can click on and move to whatever x value they want. I can create the
> line but how do I assign it's position to a particular x value???
>
> Any ideas would be appreciated.
>
> Blaine
>
>
> --
> gerein
> ------------------------------------------------------------------------
> gerein's Profile:
> http://www.excelforum.com/member.php...o&userid=25979
> View this thread: http://www.excelforum.com/showthread...hreadid=393458
>

Thanks Ed. I got that to work.

Is there any way to make the whole line selectable and not just the bottom point???

Actually, I guess what I needed were the y values at an instance of time on the x scale. The way it is set up now I can see the x value.

Ho do I get the y-intercept by using the y error bar???

The Y error bar gives you X, and you can do some kind of regression or
interpolation to allow you to compute Y from X.

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


gerein wrote:

> Actually, I guess what I needed were the y values at an instance of time
> on the x scale. The way it is set up now I can see the x value.
>
> Ho do I get the y-intercept by using the y error bar???
>
>

Hi gerein,

> Is there any way to make the whole line selectable and not
> just the bottom point???

No. You can make the bottom point easier to select by giving it a nice big
marker.


> Actually, I guess what I needed were the y values at an instance of time
> on the x scale. The way it is set up now I can see the x value.
>
> Ho do I get the y-intercept by using the y error bar???

To get the y-intercept remember your high school trig y = m.x + b

Its actually a little tricky because you need to find the two points either
side of the vertical line first. I have placed a sample called 'Intercept
Vertical Lines' on my site at
http://edferrero.m6.net/charting.aspx

Ed Ferrero
http://edferrero.m6.net/

this is a loud thinking of a non-expert of how to have moving vertical line.
may be not relevant to the problem posed by the orginator of the thread.

why not create a vertical line according to Peltier/ Tushar mehta
and use a spin button to change the dates by one number so that vertical
line moves
I have prepared a sample chart . may not ;be sophiticated solution.


--
remove $$$ from email addresss to send email

==============================
Jon Peltier <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
news:OhhGFSimFHA.1444@TK2MSFTNGP10.phx.gbl...
> The Y error bar gives you X, and you can do some kind of regression or
> interpolation to allow you to compute Y from X.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> gerein wrote:
>
> > Actually, I guess what I needed were the y values at an instance of time
> > on the x scale. The way it is set up now I can see the x value.
> >
> > Ho do I get the y-intercept by using the y error bar???
> >
> >