+ Reply to Thread
Results 1 to 4 of 4

Trying to plot multiple columns from multiple rows as a continuous line chart

  1. #1
    Registered User
    Join Date
    07-18-2015
    Location
    Near London, UK
    MS-Off Ver
    Microsoft Office 2011 (Mac)
    Posts
    2

    Question Trying to plot multiple columns from multiple rows as a continuous line chart

    Hi,

    I've been given a huge chart covering about 150 years of monthly records laid out as individual rows for each year with each month as a column. So, the sheet looks like:

    YEAR 1 January, February, March, April... ...November, December
    YEAR 2 January, February, March, April... ...November, December
    ...
    ...
    YEAR LAST January, February, March, April... ...November, December

    What I'd like to do is plot all of the data for individual months as a continuous line starting with YEAR 1 January and ending in YEAR LAST December. The chart would plot the twelve monthly points for YEAR 1 then the same line would plot the months in YEAR 2, then YEAR 3 and so on to the bottom of the sheet.

    Is there a way of doing this in Excel? I haven't found an obvious way of doing it (I'm on Macintosh version 2011 if that is an extra complication).

    Many thanks in advance,

    Mike.

  2. #2
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Trying to plot multiple columns from multiple rows as a continuous line chart

    It will probably be easiest to 'straighten out' the data in a separate area to use for charting.

    chart_matrix_data.xlsx

    In the example below, I have 6 years of source data laid out in matrix-format as per your query in cells A1:M7

    chart_matrix_data.jpg

    The cells A11:B82 contain the 'straightened' data. In this case, 6 years x 12 months, means that I need 72 rows (months) of chart data. You will need 150 years x 12 = 1800 rows.

    Just copy the formulas in A11 and B11 down for as many months as you have and adjust your range references.

    A11: =IF(ROWS(A$11:A11)=1,DATE($A$2,1,1),DATE(YEAR(A10),MONTH(A10)+1,1))
    B11: =INDEX($B$2:$M$7,INT((ROWS(B$11:B11)-1)/12)+1,MOD(ROWS(A$11:A11)-1,12)+1)

    I hope this helps.

  3. #3
    Registered User
    Join Date
    07-18-2015
    Location
    Near London, UK
    MS-Off Ver
    Microsoft Office 2011 (Mac)
    Posts
    2

    Re: Trying to plot multiple columns from multiple rows as a continuous line chart

    WOW!

    I honestly didn't expect such an amazing response - THANK-YOU for all the detail and the worked example - that is EXACTLY what I needed to do.

    I don't know how long that took, but you are incredibly kind - I was just about to break open the Python book and do it all programmatically, which would probably have ended in disaster.

    So thank you of a) fixing my problem, and b) saving my sanity!

    Much appreciated.

    Mike.

  4. #4
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Trying to plot multiple columns from multiple rows as a continuous line chart

    Sure thing; just having fun.

+ 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. help: Chart for multiple rows/columns for every run
    By samumr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-20-2015, 09:23 AM
  2. Continuous line graph using multiple rows of data?
    By Joni in forum Excel Charting & Pivots
    Replies: 15
    Last Post: 02-09-2015, 02:35 PM
  3. Trying to create a line/scatter plot of multiple columns (no rows)
    By exoscoriae in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 11-04-2014, 04:32 PM
  4. How to plot a single continuous line with data from multiple row/column?
    By crease123 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 01-02-2013, 05:48 PM
  5. Charting continuous data from multiple rows and columns
    By garethh in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-28-2012, 05:41 PM
  6. Replies: 0
    Last Post: 02-26-2012, 05:20 AM
  7. [SOLVED] I want to draw a line through the columns of a chart, plot the o.
    By Dewey in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-14-2005, 08: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