# Thread: graph a line (2000)

1. ## 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. ## 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.

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. ## 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. ## 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. ## 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. ## 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. ## 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
•