+ Reply to Thread
Results 1 to 8 of 8

Can't plot the line graph based on the data in the table

  1. #1
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Can't plot the line graph based on the data in the table

    Hello!

    My system Mac OS 10.11.6, Excel 14.7.5

    Very unexpectedly I came across numerous issues with plotting graphs based on the data in the table. I am trying to plot the same graph I have in the file (it is from the text book), but my one comes out ugly and unreadable. First, I wanted to show in the file my attempts, but then I simply deleted all of them because they are incorrect and don't add any value.

    I will be very grateful if you can guide me through the process of creating the same graph I have in the attached file:

    - how did they get all these lines crossing at one point;
    - how did they set x-axis values to the range -0.45% to 0.45% if they choose the range from the table that has only -0.30% to 30%;
    when I open "Format axis" window, I can't select such ranges;
    - how did they set all labels at the right end of each line?

    The only things I seem to know here is how to delete initial grids, and to change the color.

    Thank you very much for your time and help!
    Attached Files Attached Files

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

    Re: Can't plot the line graph based on the data in the table

    First, I wanted to show in the file my attempts, but then I simply deleted all of them because they are incorrect and don't add any value.
    I will disagree, I think it would have been useful to see what you were trying.

    1st response. What chart type are you wanting to use? Your title suggests a line chart, as does your second bullet point. The chart in your sample file is an XY scatter chart. My first guess at your problem is that you are confused or unclear about the differences and similarities between line and scatter chart types. A couple of resources:
    http://peltiertech.com/line-charts-vs-xy-charts/
    http://peltiertech.com/Excel/ChartsH...ValueAxis.html

    1st bullet point -- the NPV for each data series at X=0% is 1048, which is why they all cross in the middle.

    3rd bullet point -- By adding a data label to a single point in the data series and formatting that label to show the series name.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Can't plot the line graph based on the data in the table

    Quote Originally Posted by MrShorty View Post
    I will disagree, I think it would have been useful to see what you were trying.

    1st response. What chart type are you wanting to use? Your title suggests a line chart, as does your second bullet point. The chart in your sample file is an XY scatter chart. My first guess at your problem is that you are confused or unclear about the differences and similarities between line and scatter chart types. A couple of resources:
    http://peltiertech.com/line-charts-vs-xy-charts/
    http://peltiertech.com/Excel/ChartsH...ValueAxis.html
    Thank you very much for your reply, and for these links. I will now study them to make sure that I don't confuse two types of charts.

    Quote Originally Posted by MrShorty View Post
    1st bullet point -- the NPV for each data series at X=0% is 1048, which is why they all cross in the middle.
    Oh, of course. I definitely understand this. The graph I tried to create didn't look like the one on the picture - it was far from it. I will now try it again upon reading the articles you suggested. My main goal is to create exactly the same graph as the one I showed, and I don't understand how to do that (quite a few attempts showed that I can't).

    Quote Originally Posted by MrShorty View Post
    3rd bullet point -- By adding a data label to a single point in the data series and formatting that label to show the series name.
    So I thought, but no, I couldn't get the same result, but most likely because my graph didn't work out the way it should.

  4. #4
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Can't plot the line graph based on the data in the table

    I am attaching the excel file again, now with my attempt, which is by far much more successful now. :-)

    (1) First, I highlighted the data in the table B27:H30, and clicked to create a straight line scatter chart. This produced a horrific result.

    (2) Next, I opened Select Data box, and manually created all series by changing XY-values: Equip., Price, Units, Vc/Unit, NonVc, r

    (3) Now I need to learn how to get to the same view the original chart has:

    3.1. move xy values to the bottom and to the left respectively, as it is on the chart; at the moment I have not yet deleted the X axis just in case;
    EDIT: done :-) New file attached.

    3.2. upon that move draw these nice lines indicating XY values (semi rectangle), and add names NPV and Deviation as they are added on the original chart;
    3.3. put data labels as series names to the right of each line; please, see a screen shot attached - somehow I get each label repeated three times, and they are not placed in the same way as on the original chart.

    I will be grateful for help.
    Thank you very much.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Vitalite; 06-19-2017 at 06:38 AM. Reason: Updated the file

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

    Re: Can't plot the line graph based on the data in the table

    If you are still around, I came back to see how you were doing. I am not certain what question, if any you still have. You still show data labels on each point, which is how Excel adds data labels when you add them. It looks like the only thing left to do is to delete the extra data labels. Select each individual data label (you will often need to click on a data label twice, as the first click will select all of the data labels for that series) and press delete. Repeat for all of the unwanted data labels.

  6. #6
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Can't plot the line graph based on the data in the table

    Quote Originally Posted by MrShorty View Post
    If you are still around, I came back to see how you were doing. I am not certain what question, if any you still have. You still show data labels on each point, which is how Excel adds data labels when you add them. It looks like the only thing left to do is to delete the extra data labels. Select each individual data label (you will often need to click on a data label twice, as the first click will select all of the data labels for that series) and press delete. Repeat for all of the unwanted data labels.
    Thank you very much for coming back to me with this issue. Yes, it is exactly the one you addressed. But is it normal to manually delete those additional labels? Is it all right that those labels are displayed a few times instead of just one time? It seems that I make some basic mistake, but I truly don't see it. I checked all series' data in my file and in the original file, all look the same.

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

    Re: Can't plot the line graph based on the data in the table

    It depends on how you added the labels and all of that.

    If you select the chart and then click on add data labels, then Excel will add data labels to all points in all series within the chart.
    If you select a single data series and click on add data labels, then Excel will add data labels to all points in that series.
    If you select a single data point (as I noted in the last post, you usually need to click on a point twice to get a single point selected) and click on add data labels, then Excel will add the data label to the point selected.

    If you did either of the first two, then, yes, it is normal to need to delete the extra data labels.

  8. #8
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Can't plot the line graph based on the data in the table

    Quote Originally Posted by MrShorty View Post
    It depends on how you added the labels and all of that.

    If you select the chart and then click on add data labels, then Excel will add data labels to all points in all series within the chart.
    If you select a single data series and click on add data labels, then Excel will add data labels to all points in that series.
    If you select a single data point (as I noted in the last post, you usually need to click on a point twice to get a single point selected) and click on add data labels, then Excel will add the data label to the point selected.

    If you did either of the first two, then, yes, it is normal to need to delete the extra data labels.
    I see. Thank you very much for your help! )

+ 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. Making one line graph with two different time interval data from 2 plot
    By sohel_for in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-30-2016, 11:10 PM
  2. VBA code to plot the line graph for each row data in a worksheet
    By krkarthik in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2015, 12:14 AM
  3. Macros to plot data in a line graph
    By taimysho0 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-23-2014, 05:27 PM
  4. [SOLVED] How do you plot real time data into a line graph?
    By jonathan@ibbotsons in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 10-30-2013, 09:49 AM
  5. Data Plots Generate Plot Graph with Solid / Dash Line
    By batmanfan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2010, 12:13 AM
  6. Replies: 1
    Last Post: 12-16-2008, 08:44 AM
  7. [SOLVED] How do I plot both line and column data on same graph?
    By Jackie in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-03-2005, 01:06 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