+ Reply to Thread
Results 1 to 10 of 10

How to overlay graphs with different years in x axis

  1. #1
    Registered User
    Join Date
    09-22-2017
    Location
    belgium
    MS-Off Ver
    2013
    Posts
    6

    How to overlay graphs with different years in x axis

    I have production data for dates for different years (daily data).

    I would like to overlay the graphs with in x the data per day for each year (from January 1st till december 31st) and in y the production data. With this I am hoping to have curves for each year on the same graph.

    I don't want the curves of each year to follow each other!!

    I also made a filter because I don't want to use zeros on my graphs (already done). So the data on x aren't always the same (since I have dates without anything a year but the next year I do...)

    Is there a way to plot these curves in excel without using VBA?

    I was thinking doing a column with dates without the year but that doesn't work (since leap year exists :p )

    Hope this is clear enough as question...

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    953

    Re: How to overlay graphs with different years in x axis

    Hi Avator,

    Interesting problem, could you name the X Axis as 'Day1', 'Day2' with the field name as '2016', '2017' etc & if you come across an extra date you average the 2 results?

    Just a thought.
    Remember you are unique, like everyone else

  3. #3
    Registered User
    Join Date
    09-22-2017
    Location
    belgium
    MS-Off Ver
    2013
    Posts
    6

    Re: How to overlay graphs with different years in x axis

    Hi noboffinme,

    I was more thinking of putting on my x-axis Jan 1st, Jan 2nd, Jan 3rd, etc. but everytime excel adds the year.

    Is it not possible to just have to month and date, regardless of the year?

    I think this will be easier to overlay my different graphs, don't you think?

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,890

    Re: How to overlay graphs with different years in x axis

    Use formula to build chart data using a leap year to get full set of days.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    09-22-2017
    Location
    belgium
    MS-Off Ver
    2013
    Posts
    6

    Re: How to overlay graphs with different years in x axis

    Andy Pope,

    Looks pretty awesome what you did.

    I have one question still remaining about your file : How can I do to not show the zeros and the #N/A on the graph?

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,890

    Re: How to overlay graphs with different years in x axis

    you will have to post small example of your type of data in order to answer that.

    #N/A should not display, although the data point will be interpolated between valid points.

    Also check the settings for hidden and empty cells on the Select Data dialog.

  7. #7
    Registered User
    Join Date
    09-22-2017
    Location
    belgium
    MS-Off Ver
    2013
    Posts
    6

    Re: How to overlay graphs with different years in x axis

    Exemple.JPG

    This a example of some of my data with one cell with the formula I used
    Attached Images Attached Images

  8. #8
    Registered User
    Join Date
    09-22-2017
    Location
    belgium
    MS-Off Ver
    2013
    Posts
    6

    Re: How to overlay graphs with different years in x axis

    Exemple 2.JPG

    This is the graph

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,890

    Re: How to overlay graphs with different years in x axis

    is your chart a stacked line? if so then there is nothing you can do.

  10. #10
    Registered User
    Join Date
    09-22-2017
    Location
    belgium
    MS-Off Ver
    2013
    Posts
    6

    Re: How to overlay graphs with different years in x axis

    I found a solution :

    You add a column with v and the data that had zero you creat a cell with =IF(cell="v",IFERROR(cell,0),0). You just have to change whenever a data needs a v or not manually.
    I then created a new column to change the 0 of my calculation to #N/A using : =IF(SUM(cell)=0,NA(),SUM(cell)).

    Then I created a new sheet where I put in a column the data for every year separetly. And I created a column with every date on a year (I created a counter) without the year.

    Then you can plot a scatter for each year and it works perfectly :D

    Thank you all 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. 2 Line Graphs from different years on one chart?
    By nobodyukno in forum Excel General
    Replies: 1
    Last Post: 02-16-2017, 11:14 AM
  2. Overlay two years in bar chart
    By jimmyqwerty in forum Excel General
    Replies: 2
    Last Post: 02-07-2012, 03:44 AM
  3. 3rd Axis Line Graph Overlay Data Set Problem
    By joe.teixeira in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-11-2011, 03:26 AM
  4. Replies: 1
    Last Post: 12-05-2005, 08:35 AM
  5. overlay two graphs with different x axis
    By iconic83 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-13-2005, 11:05 AM
  6. Increasing X-axis Offset for graphs cutting x-axis
    By Hari Prasadh in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-23-2005, 05:05 AM
  7. Replies: 2
    Last Post: 08-12-2005, 08:05 AM

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