Select data from a spreadsheet column if condition in a 2nd spreadsheet at the same time is met

[wassmer]

Hi guys,

I have two spreadsheets, one gives me the beginning and end of civil twilight as a measure of day vs. night. The spreadsheet has Date/Time in the first column, and the value 45 in the 2nd column when it is night. The second spreadsheet has also 2 columns with date/time and body temperatures of a squirrel. I want to get basic statistics (mean and standard deviation) of the squirrel's nocturnal body temperature, that is for times when it is night (value 45). The tricky part is that Date/Time of both spreadsheets are different. The procedure has to recognize that the date/time of body temperature lies between the beginning and end of the value 45 blocks of the first spreadsheet. I hope someone can figure this out easily - I have no idea...

files: twilight sheet squirrel temperature

Thanks in advance!

Tom

 

[PawelOrlinski]

I think the best idea is to convert both date/time columns to the same text format and then apply vlookup function.
Best,
Paweł Orliński

 

[wassmer]

Thanks Pawel,

Problem is that the dates/times of the day-night spreadsheet are not (always) the same dates/times of the temperature file. It would be a lot of manual work to merge them into one spreadsheet. Then I still will need to have a condition like if LD (day/night) is 45 than 1, else 2 - then I could do the stats. I just want to avoid the manual assembly of both sheets...

Thanks in advance for any further info!

Tom

 

[PawelOrlinski]

I will desribe what I have done and it works well.
First, I copy-pasted squirrel data into twilight spreadsheet.
Then I changed data type of column with dates to data. The problem is that cells A182:A7809 are in different format
I used formula:

=20&MID(A182,7,2)&"-"&MID(A182,4,2)&"-"&MID(A182,1,2)&" " &MID(A182,10,5)

to convert this unpleasant format to "yyyy-mm-dd hh:mm", then i copied the whole column into another (as a value) and added to each cell zero (this addition forced date format coversion)
Afterwards I used vlookup on the column with temperature to find out which hours corresponds to night and finally I created pivot table on the second page (you can also insert table and filter data to see only night temperatures)

Here is my spreadsheet:
Index of /~po281665/Excel
Please check it and let me know whether it works.

Best

 

[wassmer]

Hi Pawel,

Looks perfect. Only question I have is how do I use other data in the Pivot table. I replaced the data in sheet 1 but this does not update data in sheet 2?

Thanks, Tom

 

[wassmer]

OK - I had to refresh data to get the new sheet into the pivot. I compared that with results from descriptive stats grouped for day/night in OriginLab Origin and it is slightly different. Totals are the same same but slight difference in 45 vs. 0 samples resulting in differences in averages and SDs. However, the SD values from your file seem to be more believable...

Thanks a lot! Tom

 

[PawelOrlinski]

Great! Some data of squirrel temperature are negative - maybe that's the point?

Best
Paweł