Record Max Temp Each Day

[roamanzambia]

I have a spreadsheet that collects data on temperature every 5 minutes each day for several months. The date/time is in column A and the temp is in column B. I need a way to find the max temp each day and place the date (once each day) in column C and the max temp for that day in column D. column C and D will be much shorter columns (as there will only be one cell of temp data for each day).
Thanks for your help!

[Glenn Kennedy]

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Tony Valko]

One way...

Data Range

	A	B	C	D	E
1	Date/Time	Temp	------	Date	Max Temp
2	1/1/2015 12:00 AM	48		1/1/2015	48
3	1/1/2015 12:05 AM	48		1/2/2015	54
4	1/1/2015 12:10 AM	47		1/3/2015	46
5	1/1/2015 12:15 AM	46
6	1/1/2015 12:20 AM	46
7	1/2/2015 12:00 AM	52
8	1/2/2015 12:05 AM	53
9	1/2/2015 12:10 AM	54
10	1/2/2015 12:15 AM	53
11	1/2/2015 12:20 AM	53
12	1/3/2015 12:00 AM	42
13	1/3/2015 12:05 AM	43
14	1/3/2015 12:10 AM	44
15	1/3/2015 12:15 AM	44
16	1/3/2015 12:20 AM	46

Enter this formula in D2:

=INT(A2)

Format as Date

Enter this array formula** D3 and copy down until you get blanks:

=IFERROR(1/(1/MIN(IF(INT(A$2:A$16)>D2,INT(A$2:A$16)))),"")

Format as Date

Enter this array formula** in E2 and copy down until you get blanks:

=IF(COUNT(D2),MAX(IF(INT(A$2:A$16)=D2,B$2:B$16)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[roamanzambia]

Thanks so much. I am putting what you gave into a practice spreadsheet but it is not working. I am attaching it so you can look at it. I do appreciate this.

[Tony Valko]

The formulas in D3 and E2 are array formulas.

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you must use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

So, enter those formulas as array formulas then copy them down until you get blanks.

[Glenn Kennedy]

You didn't enter it as an array. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[Tony Valko]

If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer.

That is an incorrect statement. They need to fix it (and I have suggested they do it a few times)!

It is possible to get a correct result depending on the circumstances.

[Glenn Kennedy]

Here's a non-array version to calculate the maximum tem.

=AGGREGATE(14,4,$B$2:$B$16*(INT(A$2:$A$16)=D2),1)

[Glenn Kennedy]

I didn't know that - do you happen to have a reference to an example?

[Tony Valko]

There was a post a couple of weeks ago that demonstrated this perfectly.

I'll see if I can find it but it will be later this evening as I have to go offline for several hours.

[Tony Valko]

See this:

https://www.excelforum.com/showthread.php?t=1085118

[roamanzambia]

Thank you so much! That worked perfectly. I was not aware of array formulas.
Is there a link somewhere that give me more of an introduction to array formulas?
I did not try the aggregate function but hope to in the future.
Thanks again for everyone's input.

[Tony Valko]

Is there a link somewhere that give me more of an introduction to array formulas?

Here ya go:

http://cpearson.com/excel/ArrayFormulas.aspx