+ Reply to Thread
Results 1 to 5 of 5

Adding Calculated Average Line to Chart

  1. #1
    Registered User
    Join Date
    10-05-2017
    Location
    Sweden
    MS-Off Ver
    Office 365 (v15.33)
    Posts
    4

    Unhappy 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.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,292

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    10-05-2017
    Location
    Sweden
    MS-Off Ver
    Office 365 (v15.33)
    Posts
    4

    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.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,292

    Re: Adding Calculated Average Line to Chart

    Glad that fixed it.

    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/

  5. #5
    Registered User
    Join Date
    10-05-2017
    Location
    Sweden
    MS-Off Ver
    Office 365 (v15.33)
    Posts
    4

    Re: Adding Calculated Average Line to Chart

    Thank you, I'll check out the article.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] adding an average line to Stacked Area in 3-D chart
    By fight2 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-11-2015, 01:52 PM
  2. Replies: 6
    Last Post: 11-26-2012, 05:17 AM
  3. Adding an average horizontal line
    By booo in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 10-20-2010, 04:33 AM
  4. Pivot Table - Adding Average as calculated field
    By Matt1234au in forum Excel General
    Replies: 4
    Last Post: 12-22-2009, 08:50 PM
  5. Straight Average Line on Line Chart
    By miked79 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-11-2008, 12:00 PM
  6. [SOLVED] Adding an average line to a bar chart
    By tylercoats in forum Excel General
    Replies: 2
    Last Post: 02-15-2006, 10:45 AM
  7. [SOLVED] How do I get a value of a calculated point on an excel line chart?
    By klc6778 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-25-2005, 10:05 PM

Tags for this Thread

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