Results 1 to 7 of 7
Thread: graph a line (2000)

20040527, 21:33 #1
 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

20040527, 21:45 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20040527, 21:46 #3
 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

20040527, 22:23 #4
 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

20040527, 22:33 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20040527, 22:51 #6
 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

20040527, 22:55 #7
 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