Excel 2007: Dynamic Vertical Line in Chart from Y Value

[praetorian75]

The background:

I've got a line chart in Excel 2007 that tracks a daily sales number. X axis is months, y is the number. Also on the chart is a trendline based on the sales number.

I currently have two manually drawn vertical lines on the chart, representing where the trendline and the two desired sales numbers intersect. These lines are vertical because I want to know at what point in the months that this happens. I manually move these lines as the daily sales number changes.

What I'd like is to automate this "move" process. Is there a way to include these two vertical lines in a dynamic way from data on the spreadsheet?

Any help is greatly appreciated. Thanks!

Jason

 

[Excelestial]

Yes...you'll need to include a column in your data table that will calculate this intersection. You can also include a column that calculates the trend...depending on the kind of trend you'd like. Simply include these two columns in your charting...be sure to chart them as a different "type" of chart (line chart). If you find the values to be outliers (by virtue of the size of the other values)...then use your secondary axis.

 

[praetorian75]

Yes...you'll need to include a column in your data table that will calculate this intersection. You can also include a column that calculates the trend...depending on the kind of trend you'd like. Simply include these two columns in your charting...be sure to chart them as a different "type" of chart (line chart). If you find the values to be outliers (by virtue of the size of the other values)...then use your secondary axis.

Thanks for replying!

I'm not sure how to implement your solution.

• How do I calculate the intersection? Right now, I'm just looking at the y-axis, then moving the vertical line until it intersects the trendline at that point on the y-axis.
• How do I calculate the trend? I'm just using excel's Trendline feature at the moment.
• If I use a line chart type, will the data be graphed as a regular line or a vertical one? I need the vertical.

 

[Steve=True]

Hi Praetorian75 (Are you roman? )

Here is a video and step by step tutorial showing you what you want.

How to draw 2 intersecting lines (Horizontal and Vertical) in a chart
http://www.exceldashboardtemplates.com/?p=572

Steve=True