Comparing data from same day in different years

[jiub]

I've got a ton of local humidity data from several different years.

What I'd like to do is subtract one data set from another to get the humidity difference between 2009 and 2008. For example, if 6/27/09 2:15pm was 75% and 6/27/08 2:15pm was 65%, I would get a difference of 10.

Unfortunately, some points and days are missing so a straight subtract won't work as it doesn't take long time time and dates in each set to deviate significantly. Every data point is taken at :15, :35, and :55 past the hour.

So basically, if a specific day and time exists in one year I would like to subtract them, and if one or the other is missing, do nothing.

[ChemistB]

Do you have them in separate workbooks, or on separate sheets within the same workbook?
Do you have date and time in column A and value in B?
So you need an exact match in Date And Time (+1 year) in order to subtract?

[Geomarsh]

Hi, you can use an IF statement

Where A1 is your 2009 value and B1 is your 2008 value:

=IF(A1="","",IF(B1="","",A1-B1))


Put this into C1 it will produce the difference, or will show nothing if one of the columns have nothing in them.

Extend this down the entire column it will produce values, Take the AVERAGE at the bottom of this column.

[jiub]

1) They are on different worksheets in the same book.

2) Date/Time is in column C and the humidity value is in column F

3) Yes, it needs to be an exact match.

Thanks

[jiub]

Hi, you can use an IF statement

Where A1 is your 2009 value and B1 is your 2008 value:

=IF(A1="","",IF(B1="","",A1-B1))


Put this into C1 it will produce the difference, or will show nothing if one of the columns have nothing in them.

Extend this down the entire column it will produce values, Take the AVERAGE at the bottom of this column.

I tried something similar, but unfortunately the data wasn't left blank, it was just skipped. i.e. it will jump from 6:15pm to 8:35pm with no blank cells in the middle. It works for a while, but eventually the skipped data adds up and I'm comparing humidity at noon to 10pm which really throws off the results.

[ChemistB]

Try something like this

Please Login or Register  to view this content.

where Sheet1!$C$2:$F$70 is your 2008 data (C is date, F is humidity)
and this formula is placed on the sheet with 2009 data (e.g. in column G) and dragged down. Does that work for you?

[jiub]

Try something like this

Please Login or Register  to view this content.

where Sheet1!$C$2:$F$70 is your 2008 data (C is date, F is humidity)
and this formula is placed on the sheet with 2009 data (e.g. in column G) and dragged down. Does that work for you?

I can't thank you enough, I've been stuck on this for over a day.

Two small things I had to change:

The last $F$70,0),4),"") only worked as $C$70,0),4),"")
I'm only looking at summer data; i.e. after the 2008 leap day, so the 366 needed to be a 365.

[ChemistB]

Glad it worked. Yes, that was a typo on my part when I updated it to your data columns. MATCH must be a single column or row.

The last $F$70,0),4),"") only worked as $C$70,0),4),"")

that 4 works because you're dates are in numerical order. I usually keep it as 0.