Moveable Vertical Line

[gerein]

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

 

[Ed Ferrero]

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/

 

[gerein]

Thanks Ed. I got that to work.

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

 

[gerein]

Actually, I guess what I needed were the y values at an instance of tim
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??

 

[Jon Peltier]

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/
_______

 

[Ed Ferrero]

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/