+ Reply to Thread
Results 1 to 3 of 3

Charting continuous data from multiple rows and columns

  1. #1
    Registered User
    Join Date
    08-28-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    1

    Charting continuous data from multiple rows and columns

    Hi Everyone,

    First post of here and hoping that after a while I too may be able to return the favour one day as my knowledge of excel increase so my thanks in advance.

    I am trying to produce a line graph which plots energy consumption over a 12 month period.

    I have recieved a spreadsheet for the energy company which record the usage every half hour for every day of the year. This is displayed as below:

    DATE 0030 0100 0130 0200 0230 0300 0330 0400
    01/07/2011 44.8 44.8 44.4 44.2 44 43.8 44 44
    02/07/2011 41.4 41.8 41.6 41.6 41.8 41.4 41.6 41.8
    03/07/2011 41.4 42.8 42 41.2 41.8 41.8 41.4 41.4
    04/07/2011 41 41.4 42.4 41.2 41 41 40.6 40.6
    05/07/2011 41.8 41.4 41.8 41.4 41.6 41.8 41.4 41.4
    06/07/2011 43.8 43.4 43.4 43.4 43 43.6 43.2 43.8
    07/07/2011 44.4 44.6 44.2 44.6 44.6 45.4 44.6 44.6
    08/07/2011 44.2 44.6 44.2 44.6 44.8 44.2 44.6 44.4

    As you can see every day is on a seperate line and includes 48 seperate readings (only small bit of data displayed), as you can there is a huge amount of data for the whole year.

    However, when i try to plot this on a graph it wants to plot a different line for every day, what I would like to do is plot a continous line for the whole year, plotting every half hour point for every day.

    How would I go about doing this?

    Any advise or tips would be greatly apprecited. Apologies in advance if i have not explained myself very well above.

    Regards

    Gareth

  2. #2
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Charting continuous data from multiple rows and columns

    I would setup 3 new columns:

    1) One with dates;
    2) One with timestamps;
    3) One with energy consumption values

    This would mean that each date should be repeated for each timestamp.

    And then use, e.g., =INDEX(MATCH()) to return the correct values from your table, as depicted above, in the third new column. Then you can use the new columns as data for a graph.

    EDIT: The best solution might actually be to use pivottable/pivotchart.
    Sincerely
    S?ren Larsen

    "Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"

  3. #3
    Forum Contributor
    Join Date
    02-22-2011
    Location
    Rhône Alpes, France
    MS-Off Ver
    Excel 2007
    Posts
    201

    Re: Charting continuous data from multiple rows and columns

    Hi,
    Convert your data with a space separator and format hours
    Insert a pivot table with wizard to create the right table
    Insert the final pivot table and pivot chart and format as desired
    Note : you will have a huge chart for the whole year
    Hope this help
    Best regards
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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