2

I can’t figure out how to compare two data sets with date intervals form each year.

Only the start and end date are the same days, the rest is spread over the period. And there can be more points in one data set than the other.

They should be compared in a graph in Google Sheets.

I have made this test sheet to show my data sets.

How can I do this without changing the data sets?

1
  • Did you ever figure out how to do this well?
    – Matt Winer
    Feb 25, 2021 at 1:45

2 Answers 2

1

I know this is an old question, but I found it whilst trying to solve the same problem, so hopefully my solution might be of use.

I added two columns with formulae to calculate the year and the day of the year eg

  • =YEAR(A2)
  • =A2-DATE(YEAR(A2),1,1)+1

Then created a very simple pivot table with the year column as the columns and the day of the year as the rows, and the result column for the values. This can then be plotted as a chart.

An example based on this data can be found at https://docs.google.com/spreadsheets/d/1NK2ysAxXDBOmDUR2xGhdtKW-0tsP2bWnAoSXhx_ux5U/edit?usp=sharing

1
  • This is great. I wish it were possible to improve two things though. 1. You can't see the actual days of the year (e.g. June 1st) or even month markers (e.g. June). 2. You need an extra pivot table just for the chart.
    – Patrick
    Aug 29, 2023 at 6:35
0
  • if you can't change positions of your dataset, then the best way would be using 2 mirrored charts and slightly overlay them:

  • demo spreadsheet

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.