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:
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)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!
i get this error: "a formula in this worksheet contains one or more invalid references."
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.
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.
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?
i don't understand what this means.Originally Posted by Andy Pope
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.
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
I have a similar problem and I cannot find a solution rather than the adding another column with the same value.
I have a data series starting in 1999 and going to 2010 with different values for each year and i want to plot it together with it's average. Doing it for one series is ok, but when i have a sheet with 100+ it starts getting too big.
any ideas?
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
Alternative ways of adding a datum
http://www.andypope.info/charts/averageline.htm
I have a similar problem and I cannot find a solution rather than the adding another column with the same value.
I have a data series starting in 1999 and going to 2010 with different values for each year and i want to plot it together with it's average. Doing it for one series is ok, but when i have a sheet with 100+ it starts getting too big.
any ideas?
So you read the rules and then simply posted your question again.
I'm closing this thread!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Bookmarks