Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    graph a line (2000)

    Hello all,

    I would think this should be easy, but I can't seem to figure out how to do it. I am charting a graph of Price vs Time, (price is on Y axis), I use the average function to find the average price. All I want to do is graph the average on the same chart, so it should just be a straight line. Now my Price and Time columns are about 500 entries, I do not want to create another column that has the average in it just so I can graph a straight line. So....Ho can I make a line across the graph at the average so that it moves as the average moves???

    The graph is a regular XY graph with the points connected.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: graph a line (2000)

    You only have to create a table with four cells: the minimum and maximum x value in the first column and the average of the 500 entries repeated in the second column.

    Click on your chart.
    Select Chart | Add Data...
    Point to the 4 cell range, and click OK.
    In the next dialog, tick "Categories (X values) in the first column", and leave "Replace existing categories" clear.
    Click OK.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: graph a line (2000)

    The simple set up:
    Col A is A2:A500 is time
    col B is B2:B500 price
    In C1 enter
    =average(B2:B500)
    In C2 enter:
    =$C$1
    Copy C2:C500

    Great the chart from cols A, B &C

    Format as desired.

    As the point sin B change the average will change.

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: graph a line (2000)

    Well, I think the first solution may be a little better for my application, the second solution involves making a third column which I don't really want to do, that would make my spreadsheet look ugly. Hans, can your method be changed a little bit? Because this is a spreadsheet which everyone sees, can I not choose to graph A1 and A500 versus C1 alone, that way I don't make a separate 4 cell thing you mentioned. It seems silly that Microsoft did not add functionality for this.

    thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: graph a line (2000)

    You do need two separate data values, even if the have the same value, in order tro make a series. You could populate C1 and C500 with the average, and specify that you want to interpolate missing values. Or you can place either the 2 cells I suggested, or the 500 cell column Steve suggested, in another worksheet.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: graph a line (2000)

    Personally, I prefer Hans' offered solution if you are "truly" using an XY scatter and not a line chart.

    The way I mentioned is the "easier way" to set up and create for an XY and also works for a Line chart. Hans' solution would have to get more elaborate if you were using a line chart (I know you mention XY, but I have seen many people "think" they are using an XY when in fact it is a line chart.) so I was covering all the bases.

    Steve

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: graph a line (2000)

    FYI,
    You need the 4 cells since a line is 2 points and a point is 2 coords. So with 4 cells you can define a line

    Steve

Posting Permissions

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