I need to pull out high and low temps based on (weird) date, any help please?

[Anthrobones]

Hey guys! I'm working on my thesis, and I have a lot of files with the following data type:


Date Time, GMT-04:00 Temp, °F
8/3/2011 17:40, 75.832
8/3/2011 18:40, 73.062
8/3/2011 19:40, 71.168
8/3/2011 20:40, 68.934

and I just need to pull out the highs/lows per date to graph. I'm having problems cutting the times off of the date field and coming up with a way to pull out just the data I need! I've tried =MAX(IF(Date=F3, Temp, 0)) type of formula, but can't get it to work.
Ideally, my output would be:

Date, High, Low
8/3/11, 78.33, 62.45

so it can easily be graphed. I appreciate any help you can give!

 

[circledchicken]

Hi,

I think a Pivot table might be useful here.

First, to remove the times from the first column you could use 'Text to Columns'.

• Highlight the column (excluding the header)
• Click on Text to Columns
• Select 'Delimited'
• Select 'Space' as the Delimiter
• Select the second column in the Data preview box and Choose 'Do not import column (skip)'

Now, you should have 2 columns, Dates and Temperatures.

• Select both columns (including the headers) and insert a Pivot Table.
• Drag the Date field into the Row Labels section.
• Drag the Temp field into the Values section.
• Drag the Temp field into the Values section a second time.
• Now in the table double click the first header that says 'Sum of Temp...' and change Sum to Max
• Double click the second header that says 'Sum of Temp...' and change Sum to Min

You should now have a list of dates in the pivot table with the corresponding Max and Min values for each date.

 

[taurean]

Use following Array formula, as it seems to work (confirmed by CTRL + SHIFT + ENTER).

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date Time	GMT-04:00 Temp	F
2	8/3/2011	5:40:00 PM	75.832			Date	Max	Min
3	8/3/2011	6:40:00 PM	78.062			8/3/2011	78.062	68.934
4	8/3/2011	7:40:00 PM	71.168
5	8/3/2011	8:40:00 PM	68.934
Sheet1

 

[Anthrobones]

Thanks guys, both ways seem to work! I've got 15 students all doing this by hand, so I know they will be grateful