+ Reply to Thread
Results 1 to 5 of 5

Plotting 8760 time series data on the x axis

  1. #1
    Registered User
    Join Date
    01-03-2018
    Location
    Hawaii
    MS-Off Ver
    2017
    Posts
    3

    Plotting 8760 time series data on the x axis

    Hello -

    I have 8,760 lines of data each representing one hour of consecutive energy data in a year. I would like to chart this but show months or seasons along the x axis in order to portray the information in a clearer way. Is there a function I can use to convert an hourly value to a month or season? Should I use custome x-axis labels on my chart? I am a newbie to all of this so give me the 101 version!

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

    Re: Plotting 8760 time series data on the x axis

    Difficult to say from your description. A lot would depend on exactly what kind of date/time data you have for the x axis. Can you upload or otherwise give us a better idea of what you have?

    My first thought, assuming you have real date/time serial number data (so the data is numeric) is to simply use a scatter chart. Excel will automatically choose a suitable value for the "major unit" on the x axis (for a year's worth of data, I would expect Excel to default to 40 or 50 days for the major unit).

    If your x axis data is text or something not numeric, I would probably fix it in the spreadsheet so that it is date/time serial numbers to use in the scatter chart.

    Explanation of Excel's date/time serial number system: http://www.cpearson.com/Excel/datetime.htm

    Does that help at all?
    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
    01-03-2018
    Location
    Hawaii
    MS-Off Ver
    2017
    Posts
    3

    Re: Plotting 8760 time series data on the x axis

    Hello McShorty - thanks for taking an interest! I took a look at the descriptions you referenced and I am thinking I need to convert the hour data (column 1) to the excel date-time format, but I'm not quite sure how to do that. Here is a sample of the data (about 4 days worth):

    Hour Aggregate load
    1 2039.846
    2 1969.76
    3 1741.972
    4 1618.784
    5 1489.562
    6 1603.532
    7 1585.25
    8 1623.55
    9 1572.57
    10 1571.046
    11 1546.284
    12 1610.418
    13 1523.066
    14 1617.046
    15 1631.164
    16 1648.656
    17 1611.284
    18 1713.57
    19 1908.616
    20 1935.58
    21 1700.494
    22 1491.52
    23 1465.116
    24 1543.362
    25 1640.37
    26 1651.648
    27 1593.604
    28 1547.152
    29 1475.286
    30 1489.356
    31 1453.26
    32 1531.22
    33 1578.44
    34 1788.784
    35 1886.876
    36 1897.96
    37 1846.112
    38 1851.862
    39 1900.532
    40 2360.884
    41 2521.22
    42 2414.64
    43 2017.988
    44 1453.574
    45 1520.772
    46 1651.564
    47 2106.112
    48 2217.77
    49 2209.328
    50 1980.75
    51 1583.152
    52 1513.834
    53 1491.654
    54 1551.098
    55 1509.096
    56 1580.154
    57 1786.578
    58 2081.698
    59 2152.016
    60 2246.75
    61 2224.998
    62 2617.67
    63 3143.466
    64 3460.052
    65 3206.046
    66 2405.578
    67 1817.544
    68 1439.556
    69 1410.716
    70 1445.5
    71 1544.188
    72 1668.388
    73 1695.606
    74 1569.98
    75 1518.47
    76 1457.19
    77 1428.33
    78 1420.68
    79 1370.67
    80 1448.026
    81 1647.256
    82 1980.926
    83 2187.532
    84 2200.138
    85 2458.858
    86 2937.922
    87 3121.012
    88 2983.128
    89 2579.1
    90 1979.484
    91 1687.242
    92 1498.104
    93 1373.968
    94 1439.35
    95 1544.548
    96 1563.73
    97 1619.514
    98 1551.496
    99 1461.05
    100 1359.132
    101 1289.59
    102 1306.324
    103 1290.4
    104 1388.188
    105 1585.974
    106 1897.392
    107 1932.416
    108 2048.84
    109 2063.142
    110 2144.696
    111 2026.478
    112 1951.942

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

    Re: Plotting 8760 time series data on the x axis

    So your time data is not date/time stamps, but integer hours. One way or another, the basic answer to the Excel question is to create a scatter chart from the data. The main question is what to do with the time/x axis data.

    1) Leave it as is, and plot energy usage vs. hour. If I did this, I would probably set the major unit to 720 hours, since 1 month~30 days~720 hours, so each major unit on the chart would be about 1 month.
    2) Convert time data to days by dividing the first column by 24 (=A2/24). Chart using that column for the x axis, and set major unit to 30 days~1 month. When all is said and done, there probably is not a lot of real difference between 1 and 2.
    3) If I added the appropriate starting date/serial number to option 2, I could get actual calendar dates for the x axis data. 42736 is the serial number for 1 Jan. 2017 -- if this data happens to be for last calendar year. In that case, I might use =42736+A2/4 or =DATE(2017,1,1)+A2/24 or similar. Then I could number format the date column with an appropriate date format, and format the x axis of the chart (if not linked to source) to use an appropriate date format. This might be the most pleasing, because the calendar date could then be simply read off of the chart axis.

    If this were my data, I would probably use option 3. Add a helper column with =DATE(...)+A2/24. Then insert a scatter chart using the helper column as the x axis data and the aggregate load as the y axis values.

  5. #5
    Registered User
    Join Date
    01-03-2018
    Location
    Hawaii
    MS-Off Ver
    2017
    Posts
    3

    Re: Plotting 8760 time series data on the x axis

    Thank you! I just tried the scatter plot with the major unit set at 720 and this worked out pretty good. But your solution #3 is very elegent so I am going to experiment with that. I appreciate the detailed intructions as well as the correction on the proper vocabulary. Mahalo from Hawaii!

+ 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. Replies: 2
    Last Post: 09-14-2015, 11:00 AM
  2. Replies: 4
    Last Post: 07-30-2015, 01:58 PM
  3. Plotting two data series on same axis with different start dates (years)
    By dbjones1988 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-27-2014, 04:13 AM
  4. Replies: 1
    Last Post: 05-05-2013, 10:38 PM
  5. [SOLVED] Plotting graph with 2 data series(Trouble with y-axis values)
    By mtg90 in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 09-05-2012, 07:24 AM
  6. Excel 2007 : Plotting two time data series
    By Hall in forum Excel General
    Replies: 1
    Last Post: 04-23-2012, 07:15 PM
  7. Plotting: Offsetting series with same x axis values
    By x12179x in forum Excel General
    Replies: 1
    Last Post: 09-10-2011, 07:19 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