parkerpress
Level 1 (39 points)
iWork

Q: How can I create a chart that creates new series every year when the data is not collected daily?

I collect numbers from my solar system "occasionally", and want to create a normalized graph that shows that generation on a yearly basis so I can compare fall to fall each year, etc.

 

Since the information is not strictly "daily" (just whenever I get to it), I don't know how to normalize it across multiple years.

 

For example, if I had

 

Date - Kw/day

1/15/10 - 14

4/3/10 - 18

4/18/10 - 17

6/1/10 - 19

9/21/10 - 15

12/3/10 - 9

2/7/11 - 13

5/2/11 - 16

8/17/11 - 18

11/1/11 - 12

11/2/11 - 12

1/5/12 - 9

6/4/12 - 12

 

and so on.

 

What I'd like to see is a line graph for each year from January to December with those datapoints on it. I have much more "incremental data" than I presented above, but it's on "random dates" over the last 8 years, and I'm just trying to see how each year compares to the other years.

 

To be up front, I tried to figure out how to to this in Excel as well, but couldn't figure it out there either.

 

TIA for any advice/hints/tips!

 

Steve

MacBook Pro, OS X El Capitan (10.11.3), 16Gb RAM (running 10.11.4)

Posted on Sep 4, 2016 9:26 AM

by Wayne Contello,Apple recommended
Wayne Contello Wayne Contello
Level 6 (18,960 points)
iWork
A:

This might work for you...

 

 

I placed your data in a table named "Data"

 

Create a second (in this example, titled "Table 1")

Screen Shot 2016-09-04 at 4.56.40 PM.png

 

In the table "Table 1" months are in the first column and the first row (which is a header row) contains the years-- add columns to add new years:

 

B2=IF(SUMIFS(Data::$B, Data::$A, ">="&DATE(B$1, $A2, 1), Data::$A, "<"&EOMONTH(DATE(B$1, $A2, 1),0))>0, SUMIFS(Data::$B, Data::$A, ">="&DATE(B$1, $A2, 1), Data::$A, "<"&EOMONTH(DATE(B$1, $A2, 1),0)), "")

 

this is shorthand for... select cell B2 then type(or copy and paste from here) the formula:

=IF(SUMIFS(Data::$B, Data::$A, ">="&DATE(B$1, $A2, 1), Data::$A, "<"&EOMONTH(DATE(B$1, $A2, 1),0))>0, SUMIFS(Data::$B, Data::$A, ">="&DATE(B$1, $A2, 1), Data::$A, "<"&EOMONTH(DATE(B$1, $A2, 1),0)), "")

 

select cell B2 copy

select cells B2 thru the end of F13, paste

 

now select cells A2 thru F13, and add a chart (the XY Scatter chart)

 

 

To add new years add one (or more) new columns for table "Table 1", then add the year at the top:

Screen Shot 2016-09-04 at 5.01.24 PM.png

Now single click the chart to activate the chart.  then click the button "Edit Data References"

 

now drag the data reference control handle to the right to add new years to the chart:

Screen Shot 2016-09-04 at 5.02.34 PM.png

 

You can add several years at a time so you do not have to this very often

Posted on Sep 4, 2016 7:01 PM

Close
parkerpress

Q: How can I create a chart that creates new series every year when the data is not collected daily?

  • All replies
  • Helpful answers

  • by Wayne Contello,Apple recommended

    Wayne Contello Wayne Contello Sep 4, 2016 7:01 PM in response to parkerpress
    Level 6 (18,960 points)
    iWork
    Sep 4, 2016 7:01 PM in response to parkerpress

    This might work for you...

     

     

    I placed your data in a table named "Data"

     

    Create a second (in this example, titled "Table 1")

    Screen Shot 2016-09-04 at 4.56.40 PM.png

     

    In the table "Table 1" months are in the first column and the first row (which is a header row) contains the years-- add columns to add new years:

     

    B2=IF(SUMIFS(Data::$B, Data::$A, ">="&DATE(B$1, $A2, 1), Data::$A, "<"&EOMONTH(DATE(B$1, $A2, 1),0))>0, SUMIFS(Data::$B, Data::$A, ">="&DATE(B$1, $A2, 1), Data::$A, "<"&EOMONTH(DATE(B$1, $A2, 1),0)), "")

     

    this is shorthand for... select cell B2 then type(or copy and paste from here) the formula:

    =IF(SUMIFS(Data::$B, Data::$A, ">="&DATE(B$1, $A2, 1), Data::$A, "<"&EOMONTH(DATE(B$1, $A2, 1),0))>0, SUMIFS(Data::$B, Data::$A, ">="&DATE(B$1, $A2, 1), Data::$A, "<"&EOMONTH(DATE(B$1, $A2, 1),0)), "")

     

    select cell B2 copy

    select cells B2 thru the end of F13, paste

     

    now select cells A2 thru F13, and add a chart (the XY Scatter chart)

     

     

    To add new years add one (or more) new columns for table "Table 1", then add the year at the top:

    Screen Shot 2016-09-04 at 5.01.24 PM.png

    Now single click the chart to activate the chart.  then click the button "Edit Data References"

     

    now drag the data reference control handle to the right to add new years to the chart:

    Screen Shot 2016-09-04 at 5.02.34 PM.png

     

    You can add several years at a time so you do not have to this very often

  • by parkerpress,

    parkerpress parkerpress Sep 4, 2016 7:06 PM in response to Wayne Contello
    Level 1 (39 points)
    iWork
    Sep 4, 2016 7:06 PM in response to Wayne Contello

    This is helpful in that it walks me through how to create the graph I'm looking for. I didn't explain clearly enough that the numbers that I collected are averages from the date before. So I wouldn't want to "add" the numbers for April 2010 together, I'd need to average them. I have some months that have almost daily datapoints, and other times when I barely have quarterly data.

     

    I think what I'm going to try to do is create a 265 row spreadsheet that has a average usage column that tests for missing data, then pulls from the next measurement's data.

     

    If I can get that done, then I should be able to follow your graphing instructions.

     

    Thanks again for your thorough explanation!

     

    Steve

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 4, 2016 9:22 PM in response to parkerpress
    Level 6 (18,960 points)
    iWork
    Sep 4, 2016 9:22 PM in response to parkerpress

    change the formula I previously posted to:

    B2=IFERROR(SUMIFS(Data::$B, Data::$A, ">="&DATE(B$1, $A2, 1), Data::$A, "<"&EOMONTH(DATE(B$1, $A2, 1),0))÷COUNTIFS( Data::$A, ">="&DATE(B$1, $A2, 1), Data::$A, "<"&EOMONTH(DATE(B$1, $A2, 1),0)), "")

     

    and fill as a described to get the average for each month