Getting Maximum Values for a Date

[Adecypher]

Hi,
I have an excel sheet which has three columns Date, Time and Temperature. The data is hourly recorded with the start date of 4/11/2011 and time: 12:00:00 AM and the end date of 4/13/2021 and time: 12:00:00 AM i.e. 10 years of data. I need to condense this data by day and maximum value in a month. So for example for a year let say 2011 I should only have 9 records with Date and Maximum Temperature Value.

Please see the sample data (for 5 days) below.

Advice and suggestions will be appreciated.

Test Temp Data.xlsx
	A	B	C
1	Day	Time	Temperature
2	4/11/2011	12:00:00 AM	89.62777937
3	4/11/2011	1:00:00 AM	89.83070039
4	4/11/2011	2:00:00 AM	89.99633733
5	4/11/2011	3:00:00 AM	89.79770601
6	4/11/2011	4:00:00 AM	89.77417411
7	4/11/2011	5:00:00 AM	89.74658754
8	4/12/2011	2:00:00 AM	89.703755
9	4/12/2011	3:00:00 AM	89.71455472
10	4/12/2011	4:00:00 AM	89.72420336
11	4/12/2011	5:00:00 AM	89.72695207
12	4/12/2011	6:00:00 AM	89.74372769
13	4/12/2011	7:00:00 AM	89.77702503
14	4/12/2011	8:00:00 AM	89.80854813
15	4/12/2011	9:00:00 AM	89.81030336
16	4/12/2011	10:00:00 AM	89.81454374
17	4/12/2011	11:00:00 AM	89.81543155
18	4/12/2011	12:00:00 PM	89.81564971
19	4/12/2011	1:00:00 PM	89.84937027
20	4/12/2011	2:00:00 PM	89.86433073
21	4/12/2011	3:00:00 PM	89.90774513
22	4/12/2011	4:00:00 PM	89.91678239
23	4/12/2011	5:00:00 PM	89.8968723
24	4/12/2011	6:00:00 PM	89.88171432
25	4/12/2011	7:00:00 PM	89.90538762
26	4/12/2011	8:00:00 PM	89.90920861
27	4/12/2011	9:00:00 PM	89.89170028
28	4/12/2011	10:00:00 PM	89.90048188
29	4/12/2011	11:00:00 PM	89.91579807
30	4/13/2011	12:00:00 AM	89.94183986
31	4/13/2011	1:00:00 AM	89.94780055
32	4/13/2011	2:00:00 AM	89.89907908
33	4/13/2011	3:00:00 AM	89.91624186
34	4/13/2011	4:00:00 AM	89.90061185
35	4/13/2011	5:00:00 AM	89.90005451
36	4/13/2011	6:00:00 AM	89.92957078
37	4/13/2011	7:00:00 AM	89.92594145
38	4/13/2011	8:00:00 AM	89.94034973
39	4/13/2011	9:00:00 AM	89.93831181
40	4/13/2011	10:00:00 AM	89.97381138
41	4/13/2011	11:00:00 AM	89.91008465
42	4/13/2011	12:00:00 PM	89.9221225
43	4/13/2011	1:00:00 PM	89.94248089
44	4/13/2011	2:00:00 PM	89.92203148
45	4/13/2011	3:00:00 PM	89.91160678
46	4/13/2011	4:00:00 PM	89.89667063
47	4/13/2011	5:00:00 PM	89.89071318
48	4/13/2011	6:00:00 PM	89.88855241
49	4/13/2011	7:00:00 PM	89.92544305
50	4/13/2011	8:00:00 PM	89.90270771
51	4/13/2011	9:00:00 PM	89.8626188
52	4/13/2011	10:00:00 PM	89.85639712
53	4/13/2011	11:00:00 PM	89.85573732
54	4/14/2011	12:00:00 AM	89.82276248
55	4/14/2011	1:00:00 AM	89.81841965
56	4/14/2011	2:00:00 AM	89.82992964
57	4/14/2011	3:00:00 AM	89.79677694
58	4/14/2011	4:00:00 AM	89.80410821
59	4/14/2011	5:00:00 AM	89.77753227
60	4/14/2011	6:00:00 AM	89.79805526
61	4/14/2011	7:00:00 AM	89.76632318
62	4/14/2011	8:00:00 AM	89.79313255
63	4/14/2011	9:00:00 AM	89.7954343
64	4/14/2011	10:00:00 AM	89.75527056
65	4/14/2011	11:00:00 AM	89.75959098
66	4/14/2011	12:00:00 PM	89.74041897
67	4/14/2011	1:00:00 PM	89.73745119
68	4/14/2011	2:00:00 PM	89.72748286
69	4/14/2011	3:00:00 PM	89.73357106
70	4/14/2011	4:00:00 PM	89.73978163
71	4/14/2011	5:00:00 PM	89.69373134
72	4/14/2011	6:00:00 PM	89.69243007
73	4/14/2011	7:00:00 PM	89.70108349
74	4/14/2011	8:00:00 PM	89.69143145
75	4/14/2011	9:00:00 PM	89.71144434
76	4/14/2011	10:00:00 PM	89.68669843
77	4/14/2011	11:00:00 PM	89.65879375
78	4/15/2011	12:00:00 AM	89.62574137
79	4/15/2011	1:00:00 AM	89.63699399
80	4/15/2011	2:00:00 AM	89.60454973
81	4/15/2011	3:00:00 AM	89.61356894
82	4/15/2011	4:00:00 AM	89.61858809
83	4/15/2011	5:00:00 AM	89.59192218
84	4/15/2011	6:00:00 AM	89.65967075
85	4/15/2011	7:00:00 AM	89.83517183
86	4/15/2011	8:00:00 AM	89.80151035
87	4/15/2011	9:00:00 AM	89.75513664
88	4/15/2011	10:00:00 AM	89.68092351
89	4/15/2011	11:00:00 AM	89.66758129
90	4/15/2011	12:00:00 PM	89.51657856
91	4/15/2011	1:00:00 PM	89.51700686
92	4/15/2011	2:00:00 PM	89.23626623
93	4/15/2011	3:00:00 PM	89.21921134
94	4/15/2011	4:00:00 PM	89.32373699
95	4/15/2011	5:00:00 PM	89.33945197
96	4/15/2011	6:00:00 PM	89.3443829
97	4/15/2011	7:00:00 PM	89.34052176
98	4/15/2011	8:00:00 PM	89.35225468
99	4/15/2011	9:00:00 PM	89.35955171
100	4/15/2011	10:00:00 PM	89.36179366
Sheet1

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I'm also a bit confused about what you're after. If you have data over 10 years how do you end up with 9 results when you want it on a monthly basis?

 

[Adecypher]

@Fluff
I apologize for the confusion. The excel version I am using is 2016 and to redefine my question: I need to condense the data by day with maximum temperature value.

 

[Fluff]

Ok how about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Day	Time	Temperature		Date	Temperature
2	11/04/2011	00:00:00	89.6277794		11/04/2011	89.9963373
3	11/04/2011	01:00:00	89.8307004		12/04/2011	89.9167824
4	11/04/2011	02:00:00	89.9963373		13/04/2011	89.9738114
5	11/04/2011	03:00:00	89.797706		14/04/2011	89.8299296
6	11/04/2011	04:00:00	89.7741741		15/04/2011	89.8351718
7	11/04/2011	05:00:00	89.7465875
8	12/04/2011	02:00:00	89.703755
9	12/04/2011	03:00:00	89.7145547
10	12/04/2011	04:00:00	89.7242034
11	12/04/2011	05:00:00	89.7269521
12	12/04/2011	06:00:00	89.7437277
13	12/04/2011	07:00:00	89.777025
14	12/04/2011	08:00:00	89.8085481
15	12/04/2011	09:00:00	89.8103034
16	12/04/2011	10:00:00	89.8145437
17	12/04/2011	11:00:00	89.8154316
18	12/04/2011	12:00:00	89.8156497
19	12/04/2011	13:00:00	89.8493703
20	12/04/2011	14:00:00	89.8643307
21	12/04/2011	15:00:00	89.9077451
22	12/04/2011	16:00:00	89.9167824
23	12/04/2011	17:00:00	89.8968723
24	12/04/2011	18:00:00	89.8817143
25	12/04/2011	19:00:00	89.9053876
26	12/04/2011	20:00:00	89.9092086
27	12/04/2011	21:00:00	89.8917003
28	12/04/2011	22:00:00	89.9004819
29	12/04/2011	23:00:00	89.9157981
30	13/04/2011	00:00:00	89.9418399
31	13/04/2011	01:00:00	89.9478005
32	13/04/2011	02:00:00	89.8990791
33	13/04/2011	03:00:00	89.9162419
34	13/04/2011	04:00:00	89.9006118
35	13/04/2011	05:00:00	89.9000545
36	13/04/2011	06:00:00	89.9295708
37	13/04/2011	07:00:00	89.9259415
38	13/04/2011	08:00:00	89.9403497
39	13/04/2011	09:00:00	89.9383118
40	13/04/2011	10:00:00	89.9738114
41	13/04/2011	11:00:00	89.9100846
42	13/04/2011	12:00:00	89.9221225
43	13/04/2011	13:00:00	89.9424809
44	13/04/2011	14:00:00	89.9220315
45	13/04/2011	15:00:00	89.9116068
46	13/04/2011	16:00:00	89.8966706
47	13/04/2011	17:00:00	89.8907132
48	13/04/2011	18:00:00	89.8885524
49	13/04/2011	19:00:00	89.9254431
50	13/04/2011	20:00:00	89.9027077
51	13/04/2011	21:00:00	89.8626188
52	13/04/2011	22:00:00	89.8563971
53	13/04/2011	23:00:00	89.8557373
54	14/04/2011	00:00:00	89.8227625
55	14/04/2011	01:00:00	89.8184197
56	14/04/2011	02:00:00	89.8299296
57	14/04/2011	03:00:00	89.7967769
58	14/04/2011	04:00:00	89.8041082
59	14/04/2011	05:00:00	89.7775323
60	14/04/2011	06:00:00	89.7980553
61	14/04/2011	07:00:00	89.7663232
62	14/04/2011	08:00:00	89.7931325
63	14/04/2011	09:00:00	89.7954343
64	14/04/2011	10:00:00	89.7552706
65	14/04/2011	11:00:00	89.759591
66	14/04/2011	12:00:00	89.740419
67	14/04/2011	13:00:00	89.7374512
68	14/04/2011	14:00:00	89.7274829
69	14/04/2011	15:00:00	89.7335711
70	14/04/2011	16:00:00	89.7397816
71	14/04/2011	17:00:00	89.6937313
72	14/04/2011	18:00:00	89.6924301
73	14/04/2011	19:00:00	89.7010835
74	14/04/2011	20:00:00	89.6914315
75	14/04/2011	21:00:00	89.7114443
76	14/04/2011	22:00:00	89.6866984
77	14/04/2011	23:00:00	89.6587938
78	15/04/2011	00:00:00	89.6257414
79	15/04/2011	01:00:00	89.636994
80	15/04/2011	02:00:00	89.6045497
81	15/04/2011	03:00:00	89.6135689
82	15/04/2011	04:00:00	89.6185881
83	15/04/2011	05:00:00	89.5919222
84	15/04/2011	06:00:00	89.6596708
85	15/04/2011	07:00:00	89.8351718
86	15/04/2011	08:00:00	89.8015104
87	15/04/2011	09:00:00	89.7551366
88	15/04/2011	10:00:00	89.6809235
89	15/04/2011	11:00:00	89.6675813
90	15/04/2011	12:00:00	89.5165786
91	15/04/2011	13:00:00	89.5170069
92	15/04/2011	14:00:00	89.2362662
93	15/04/2011	15:00:00	89.2192113
94	15/04/2011	16:00:00	89.323737
95	15/04/2011	17:00:00	89.339452
96	15/04/2011	18:00:00	89.3443829
97	15/04/2011	19:00:00	89.3405218
98	15/04/2011	20:00:00	89.3522547
99	15/04/2011	21:00:00	89.3595517
100	15/04/2011	22:00:00	89.3617937
Data

Cell Formulas
Range	Formula
E2:E15	E2	=IFERROR(INDEX($A$2:$A$1000,AGGREGATE(15,6,(ROW($A$2:$A$1000)-ROW($A$2)+1)/ISNA(MATCH($A$2:$A$1000,$E$1:$E1,0))/($A$2:$A$1000<>""),1)),"")
F2:F15	F2	=IF(E2="","",MAX(IF($A$2:$A$1000=E2,$C$2:$C$1000)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Adecypher]

Thanks but my computer currently is hanged and it says "processing" since I have around 85000 rows in the original work book.

 

[Fluff]

With that amount of data I'm not really surprised it's taking a while to calculate.

 

[Adecypher]

I divided the data into years and then use the formulas and completed the calculations.

Thanks

 

[alansidman]

Here is an alternative solution where I created two different queries in Power Query

Max by date

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day ", type date}, {"Time", type time}, {"Temperature", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Day "}, {{"Max Temp", each List.Max([Temperature]), type nullable number}})
in
    #"Grouped Rows"

Max by Month

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day ", type date}, {"Time", type time}, {"Temperature", type number}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([#"Day "]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([#"Day "]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Month", "Year"}, {{"Max Temp", each List.Max([Temperature]), type nullable number}})
in
    #"Grouped Rows"

Book7
	A	B
1	Day	Max Temp
2	4/11/2011	89.99633733
3	4/12/2011	89.91678239
4	4/13/2011	89.97381138
5	4/14/2011	89.82992964
6	4/15/2011	89.83517183
DayMax

Book7
	A	B	C
1	Month	Year	Max Temp
2	4	2011	89.99633733
MonthMax

 

[Fluff]

Glad you sorted it & thanks for the feedback.