# Adding Calculated Average Line to Chart

1. ## Adding Calculated Average Line to Chart

I hope someone can help because I'm starting to hate charts. I'm basically trying to add an average line to my line-graph by using calculated averages instead of the trend-line tool.

I have two tables. Table 1 has Column A (a number) and Column B (also a number). I've created a line graph of this table using Column A as the x-axis (horizontal) and Column B as the data points.

I have another table that calculates the averages of Column B if Column A falls within a certain range. ``Please Login or Register  to view this content.``
(simplified code since that's not the point of this post.)

I want to add this line to the chart. It's basically the same as adding a trend line, except I need to have the calculated number since the ranges vary. I calculated the median of the ranges and used that as my x-axis and the averages as my data points.

The result:
Screen Shot 2017-10-05 at 7.35.37 PM.png

The average line is the blue one. I think the issue is that I only have 20 or so "average" data points and thousands of the other data points. But I don't understand why the average line doesn't stretch across the graph since I've used the median of the ranges and those fall all along the x-axis (ie if I have an x-axis that goes from 1-10 and I plot points at 3, 5, and 7 I would think it should stretch out.)

Note: I also plan to find the minimum of each average range and add that as well (but I will use a formula to find the exact point that instance happens rather than the median of the average range). So I hope the solution works in both instances.  Register To Reply

2. ## Re: Adding Calculated Average Line to Chart

It is difficult to see exactly what is going on based on a limited description of the data and a picture of the messed up chart.

My first thought, where column A is a number -- is there a reason you are using a line chart rather than an XY scatter chart? I find that it is often much easier to have different series with different x values on a scatter chart than other chart types. I wonder if your problem will resolve itself by simply changing chart types.  Register To Reply

3. ## Re: Adding Calculated Average Line to Chart

Yep, that fixed it! (with a bit of tweaking)

Thank you!! I was getting pretty frustrated. I'll remember to stay away from line-graphs in the future.  Register To Reply

4. ## Re: Adding Calculated Average Line to Chart

I would not that it isn't about staying away from line-graphs. It's more about understanding the differences between line and scatter charts and when to use each one. If you are interested, this could be an interesting article to read: https://peltiertech.com/line-charts-vs-xy-charts/  Register To Reply

5. ## Re: Adding Calculated Average Line to Chart

Thank you, I'll check out the article.  Register To Reply

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