+ Reply to Thread
Results 1 to 8 of 8

Plotting straight line using a single value

  1. #1
    Registered User
    Join Date
    03-05-2008
    Posts
    21

    Plotting straight line using a single value

    Hello,

    Please refer to the attached chart.

    How do I stretch the PINK line so that it can plot the value contained in cell D2 over the entire period.

    What I am trying to do is what a guy was asking in the forum. However, the solution posted could not work.

    Thanks much.


    The question and solution from excel forum

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

    Posted by Skewer on July 18, 2001 11:22 AM

    Image the scene, you want a graph showing stuff but there is an ideal value or range of values (as in cost, weight, etc).

    How can I get a single value (already calculated elsewhere) or values to generate horizontal lines so at a glance the approach towards (or fluctuation between) certain values is visible?

    I could just draw an autoshape over the graph I guess but then how could I get that to move around?! Surely there's a chart method that doesn't involve a hidden row(s) containing solely absolute references to this value in its calculated location?

    Cheers,
    SQR



    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!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-05-2008
    Posts
    21

    plotting a straight line using a single value

    Hello,

    Please refer to the attached chart (Excel 2003).

    How do I stretch the PINK line so that it can plot the value contained in cell D2 over the entire period.

    What I am trying to do is what a guy was asking in the forum. However, the solution posted could not work.

    Thanks much.


    The question and solution from excel forum

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

    Posted by Skewer on July 18, 2001 11:22 AM

    Image the scene, you want a graph showing stuff but there is an ideal value or range of values (as in cost, weight, etc).

    How can I get a single value (already calculated elsewhere) or values to generate horizontal lines so at a glance the approach towards (or fluctuation between) certain values is visible?

    I could just draw an autoshape over the graph I guess but then how could I get that to move around?! Surely there's a chart method that doesn't involve a hidden row(s) containing solely absolute references to this value in its calculated location?

    Cheers,
    SQR



    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!
    Attached Files Attached Files

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You need two points to plot a line:

    x1 y1
    x2 y2

    If the line is horizontal, then x1 is the minimum x value for the plot, x2 is the max, and y1=y2.

  4. #4
    Registered User
    Join Date
    03-05-2008
    Posts
    21
    Thank you SHG.

    Could you show me how to do it using the spreadsheet I have attached in this thread.


    Also, how come the solution posted by below does not work?

    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!

  5. #5
    Registered User
    Join Date
    03-05-2008
    Posts
    21

    plotting a straight line using a single value

    Hello,

    Please refer to the attached chart.

    How do I stretch the PINK line so that it can plot the value contained in cell D2 over the entire period.

    What I am trying to do is what a guy was asking in the forum. However, the solution posted could not work.

    Thanks much.


    The question and solution from excel forum
    Constant horizontal line on a chart (based on a single value)

    Posted by Skewer on July 18, 2001 11:22 AM

    Image the scene, you want a graph showing stuff but there is an ideal value or range of values (as in cost, weight, etc).

    How can I get a single value (already calculated elsewhere) or values to generate horizontal lines so at a glance the approach towards (or fluctuation between) certain values is visible?

    I could just draw an autoshape over the graph I guess but then how could I get that to move around?! Surely there's a chart method that doesn't involve a hidden row(s) containing solely absolute references to this value in its calculated location?

    Cheers,
    SQR



    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!
    Attached Files Attached Files

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    I changed the chart to a scatter plot, and added this series:
    Please Login or Register  to view this content.
    There may be a way to do it with a line chart as you are trying to do -- don't know.

    You could just add a column of twos (or references to D2) and plot that as a second series on your line chart.

  7. #7
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hi,

    Does this link offer any help?

    http://peltiertech.com/Excel/Charts/...orzSeries.html
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  8. #8
    Registered User
    Join Date
    03-05-2008
    Posts
    21
    Hello,

    Thank you oldchippy and SHG.

    The method described in the link does not work for me because it uses the secondary axis, which I will need to use it for other series.

    I still prefer the method in which you can simply use a "named" constant that points to a single value in a cell to draw a line across the entire x-axis.

    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1