plotting a horizontal line based on a cell value

[justin111]

title says it all. i have a plot (graph), and i want to place a horizontal line at a particular constant value specified by me in a named cell. how can i do this? i googled around and found this:

Re: Constant horizontal line on a chart (based on a single value)

Posted by Mark W. on July 18, 2001 11:55 AM
Let's assume that your desired Y-intercept value
is in cell Sheet1!$A$1. First, create a defined
name called Sheet1!Y_Intercept_Value with a
reference of ={1,1}*Sheet1!$A$1. Select your
chart area and on the formula bar enter the formula,
=SERIES(,,Sheet1!Y_Intercept_Value,1). Y=A1 should
now be plotted on your chart!

but it didn't work. first off when i try doing it on the chart area, it fails. so when i try it on the plot area using =SERIES(,,ref,1) (where ref is the name of a cell with the value {1,1}*100)

i get this error: "a formula in this worksheet contains one or more invalid references."

[kellyfspringer]

While this is very elementary, it may provide you with the result that you are looking for:

I'm assuming that your graph is pulling from a range of cells in a spreadsheet. Why don't you add a line into your source range which you manually type in the baseline that you want to include.
See my example attached.

[justin111]

thanks for the suggestion, but i'm looking for more of a direct solution as opposed to a workaround. if i absolutely had to i'd do something like what you did, but i agree that this is elementary so i'm hoping i won't need to use a workaround.

[Andy Pope]

The named range suggest worked after a fashion. For me it plotted 2 points but that was not enough to cover the range of the chart data.
I guess you could move the series to the secondary axis in which case it would.

You can do it using a data point and error bar.

Why do you not want to use cells to make this simple?

[justin111]

The named range suggest worked after a fashion.

i don't understand what this means.

For me it plotted 2 points but that was not enough to cover the range of the chart data.
I guess you could move the series to the secondary axis in which case it would.

You can do it using a data point and error bar.

Why do you not want to use cells to make this simple?

the value that i want the horizontal line to be at will be changed frequently, as will the # of data points in the plot. i just feel like there is a more efficient solution.

[Andy Pope]

I mean it plotted 2 points at the Y value of 100. But it would not automatically update if more points where required. In order for that to happen more 1's would be needed. So for a line with 4 points it would be.

={1,1,1,1}*100

Basically this feature is not built in to charts so you have to work around it.
So it depends what you think is the easist work around. More cells, updating named range or 1 single point with error line.

This has all the variations of drawing a datum line on a chart
http://www.andypope.info/charts/averageline.htm