How to find the maximum value in a column based on month and year?

[LRATOZ]

Hi,

I have been collecting weather data for six years and I would like to make a dashboard that shows me the following:

The highest recorded temperature per month for each year.
(Similar as the record values in the climate table at Climate of Melbourne - Wikipedia)

My spreadsheet has got a column showing every dat over the last six years and has also a temperature reading for each date.

So, I want to find the highest temperature per month and per year with the date when it happened.
To find the maximum value per column is easy but I would like to see the maximum value for each month and on what date and in which year it happened.

Obviously, I record much more the just the temperature but once I know how to do this then it will be easy to apply the same formula to different data types.
So, if somebody has a solution this I would be grateful to hear about it.
Thank you very much in advance!
Luke

 

[LRATOZ]

OK, I spent more time on this and now I am on the right track
I created a simple file that has two colums:
Column A lists all dates between 01-01-2018 and 31-05-2019 (XL2BB has a limit of 3000 cells)
Column B contains a random value between 1 and 100

In C1 I got the formula: =MAX(INDEX((MONTH(date)=3)*(YEAR(date)=2018)*Value,0))

The value returned is: 77
The formula as is shows me the maximum value for the 3rd month of 2018, so, I'm on the right track.
I just need to make a slight adjustment that will show me:
The maximum value for March over all the years.
The maximum value for March 2019 is: 99
So, I would like to see the maximum value for a particular month over multiple years and with the date when it occurred.
In this case it should show me: 99 but it happened on two occasions: 27-3-2019 and 31-03-2019.
In this case I want to keep the earliest date.

Please ignore the differences in the RANDOM-functions as this is not important. I just wanted to have the cells populated with random numbers between 1 and 100 to prove a point.

I'm I asking too much from Excel? Can this be done?

2021-06-02 Weather test sheet.xlsx
	A	B	C
1	1/01/2018	32	77
2	2/01/2018	81
3	3/01/2018	6
4	4/01/2018	63
5	5/01/2018	37
6	6/01/2018	83
7	7/01/2018	60
8	8/01/2018	84
9	9/01/2018	16
10	10/01/2018	63
11	11/01/2018	56
12	12/01/2018	33
13	13/01/2018	97
14	14/01/2018	100
15	15/01/2018	95
16	16/01/2018	59
17	17/01/2018	25
18	18/01/2018	74
19	19/01/2018	22
20	20/01/2018	70
21	21/01/2018	14
22	22/01/2018	80
23	23/01/2018	88
24	24/01/2018	53
25	25/01/2018	38
26	26/01/2018	34
27	27/01/2018	44
28	28/01/2018	53
29	29/01/2018	85
30	30/01/2018	56
31	31/01/2018	2
32	1/02/2018	15
33	2/02/2018	32
34	3/02/2018	43
35	4/02/2018	29
36	5/02/2018	95
37	6/02/2018	40
38	7/02/2018	7
39	8/02/2018	79
40	9/02/2018	58
41	10/02/2018	54
42	11/02/2018	2
43	12/02/2018	78
44	13/02/2018	27
45	14/02/2018	34
46	15/02/2018	27
47	16/02/2018	73
48	17/02/2018	9
49	18/02/2018	46
50	19/02/2018	29
51	20/02/2018	87
52	21/02/2018	15
53	22/02/2018	88
54	23/02/2018	72
55	24/02/2018	84
56	25/02/2018	47
57	26/02/2018	83
58	27/02/2018	68
59	28/02/2018	19
60	1/03/2018	70
61	2/03/2018	59
62	3/03/2018	53
63	4/03/2018	27
64	5/03/2018	66
65	6/03/2018	70
66	7/03/2018	2
67	8/03/2018	74
68	9/03/2018	64
69	10/03/2018	69
70	11/03/2018	16
71	12/03/2018	53
72	13/03/2018	42
73	14/03/2018	32
74	15/03/2018	20
75	16/03/2018	58
76	17/03/2018	5
77	18/03/2018	70
78	19/03/2018	77
79	20/03/2018	65
80	21/03/2018	35
81	22/03/2018	54
82	23/03/2018	17
83	24/03/2018	60
84	25/03/2018	6
85	26/03/2018	70
86	27/03/2018	34
87	28/03/2018	7
88	29/03/2018	13
89	30/03/2018	16
90	31/03/2018	7
91	1/04/2018	22
92	2/04/2018	92
93	3/04/2018	93
94	4/04/2018	18
95	5/04/2018	22
96	6/04/2018	86
97	7/04/2018	70
98	8/04/2018	28
99	9/04/2018	80
100	10/04/2018	43
101	11/04/2018	18
102	12/04/2018	79
103	13/04/2018	63
104	14/04/2018	44
105	15/04/2018	88
106	16/04/2018	43
107	17/04/2018	25
108	18/04/2018	55
109	19/04/2018	67
110	20/04/2018	96
111	21/04/2018	50
112	22/04/2018	38
113	23/04/2018	82
114	24/04/2018	12
115	25/04/2018	25
116	26/04/2018	12
117	27/04/2018	18
118	28/04/2018	29
119	29/04/2018	16
120	30/04/2018	99
121	1/05/2018	31
122	2/05/2018	7
123	3/05/2018	69
124	4/05/2018	6
125	5/05/2018	27
126	6/05/2018	63
127	7/05/2018	47
128	8/05/2018	89
129	9/05/2018	19
130	10/05/2018	76
131	11/05/2018	26
132	12/05/2018	57
133	13/05/2018	49
134	14/05/2018	56
135	15/05/2018	83
136	16/05/2018	8
137	17/05/2018	31
138	18/05/2018	99
139	19/05/2018	53
140	20/05/2018	90
141	21/05/2018	13
142	22/05/2018	24
143	23/05/2018	70
144	24/05/2018	46
145	25/05/2018	18
146	26/05/2018	83
147	27/05/2018	87
148	28/05/2018	91
149	29/05/2018	82
150	30/05/2018	37
151	31/05/2018	78
152	1/06/2018	73
153	2/06/2018	23
154	3/06/2018	16
155	4/06/2018	92
156	5/06/2018	31
157	6/06/2018	24
158	7/06/2018	81
159	8/06/2018	7
160	9/06/2018	63
161	10/06/2018	100
162	11/06/2018	71
163	12/06/2018	12
164	13/06/2018	21
165	14/06/2018	25
166	15/06/2018	23
167	16/06/2018	81
168	17/06/2018	42
169	18/06/2018	28
170	19/06/2018	77
171	20/06/2018	91
172	21/06/2018	11
173	22/06/2018	69
174	23/06/2018	59
175	24/06/2018	97
176	25/06/2018	66
177	26/06/2018	79
178	27/06/2018	46
179	28/06/2018	31
180	29/06/2018	100
181	30/06/2018	51
182	1/07/2018	37
183	2/07/2018	83
184	3/07/2018	88
185	4/07/2018	65
186	5/07/2018	84
187	6/07/2018	22
188	7/07/2018	30
189	8/07/2018	29
190	9/07/2018	11
191	10/07/2018	9
192	11/07/2018	6
193	12/07/2018	91
194	13/07/2018	96
195	14/07/2018	54
196	15/07/2018	99
197	16/07/2018	15
198	17/07/2018	27
199	18/07/2018	22
200	19/07/2018	97
201	20/07/2018	26
202	21/07/2018	74
203	22/07/2018	40
204	23/07/2018	12
205	24/07/2018	34
206	25/07/2018	79
207	26/07/2018	81
208	27/07/2018	7
209	28/07/2018	90
210	29/07/2018	8
211	30/07/2018	64
212	31/07/2018	4
213	1/08/2018	14
214	2/08/2018	88
215	3/08/2018	74
216	4/08/2018	11
217	5/08/2018	73
218	6/08/2018	94
219	7/08/2018	27
220	8/08/2018	22
221	9/08/2018	7
222	10/08/2018	99
223	11/08/2018	79
224	12/08/2018	37
225	13/08/2018	20
226	14/08/2018	33
227	15/08/2018	35
228	16/08/2018	26
229	17/08/2018	2
230	18/08/2018	61
231	19/08/2018	21
232	20/08/2018	12
233	21/08/2018	5
234	22/08/2018	79
235	23/08/2018	95
236	24/08/2018	21
237	25/08/2018	10
238	26/08/2018	6
239	27/08/2018	58
240	28/08/2018	100
241	29/08/2018	51
242	30/08/2018	69
243	31/08/2018	82
244	1/09/2018	5
245	2/09/2018	68
246	3/09/2018	53
247	4/09/2018	10
248	5/09/2018	8
249	6/09/2018	92
250	7/09/2018	86
251	8/09/2018	74
252	9/09/2018	1
253	10/09/2018	37
254	11/09/2018	19
255	12/09/2018	45
256	13/09/2018	3
257	14/09/2018	42
258	15/09/2018	60
259	16/09/2018	16
260	17/09/2018	75
261	18/09/2018	10
262	19/09/2018	23
263	20/09/2018	19
264	21/09/2018	73
265	22/09/2018	89
266	23/09/2018	72
267	24/09/2018	11
268	25/09/2018	35
269	26/09/2018	16
270	27/09/2018	45
271	28/09/2018	69
272	29/09/2018	82
273	30/09/2018	39
274	1/10/2018	81
275	2/10/2018	85
276	3/10/2018	45
277	4/10/2018	20
278	5/10/2018	9
279	6/10/2018	35
280	7/10/2018	81
281	8/10/2018	81
282	9/10/2018	44
283	10/10/2018	57
284	11/10/2018	37
285	12/10/2018	5
286	13/10/2018	30
287	14/10/2018	22
288	15/10/2018	79
289	16/10/2018	90
290	17/10/2018	35
291	18/10/2018	99
292	19/10/2018	68
293	20/10/2018	39
294	21/10/2018	89
295	22/10/2018	83
296	23/10/2018	43
297	24/10/2018	28
298	25/10/2018	100
299	26/10/2018	60
300	27/10/2018	29
301	28/10/2018	75
302	29/10/2018	30
303	30/10/2018	66
304	31/10/2018	11
305	1/11/2018	58
306	2/11/2018	44
307	3/11/2018	74
308	4/11/2018	95
309	5/11/2018	36
310	6/11/2018	72
311	7/11/2018	9
312	8/11/2018	13
313	9/11/2018	85
314	10/11/2018	22
315	11/11/2018	4
316	12/11/2018	61
317	13/11/2018	70
318	14/11/2018	52
319	15/11/2018	90
320	16/11/2018	90
321	17/11/2018	15
322	18/11/2018	91
323	19/11/2018	19
324	20/11/2018	35
325	21/11/2018	28
326	22/11/2018	83
327	23/11/2018	14
328	24/11/2018	12
329	25/11/2018	38
330	26/11/2018	66
331	27/11/2018	2
332	28/11/2018	22
333	29/11/2018	77
334	30/11/2018	15
335	1/12/2018	9
336	2/12/2018	32
337	3/12/2018	51
338	4/12/2018	73
339	5/12/2018	43
340	6/12/2018	83
341	7/12/2018	21
342	8/12/2018	100
343	9/12/2018	66
344	10/12/2018	93
345	11/12/2018	51
346	12/12/2018	28
347	13/12/2018	68
348	14/12/2018	24
349	15/12/2018	46
350	16/12/2018	62
351	17/12/2018	54
352	18/12/2018	22
353	19/12/2018	16
354	20/12/2018	33
355	21/12/2018	82
356	22/12/2018	36
357	23/12/2018	34
358	24/12/2018	71
359	25/12/2018	1
360	26/12/2018	44
361	27/12/2018	54
362	28/12/2018	58
363	29/12/2018	16
364	30/12/2018	95
365	31/12/2018	82
366	1/01/2019	88
367	2/01/2019	100
368	3/01/2019	91
369	4/01/2019	64
370	5/01/2019	44
371	6/01/2019	49
372	7/01/2019	60
373	8/01/2019	11
374	9/01/2019	91
375	10/01/2019	91
376	11/01/2019	15
377	12/01/2019	85
378	13/01/2019	21
379	14/01/2019	4
380	15/01/2019	19
381	16/01/2019	24
382	17/01/2019	51
383	18/01/2019	13
384	19/01/2019	19
385	20/01/2019	85
386	21/01/2019	84
387	22/01/2019	17
388	23/01/2019	61
389	24/01/2019	76
390	25/01/2019	40
391	26/01/2019	72
392	27/01/2019	88
393	28/01/2019	92
394	29/01/2019	36
395	30/01/2019	77
396	31/01/2019	28
397	1/02/2019	80
398	2/02/2019	1
399	3/02/2019	79
400	4/02/2019	30
401	5/02/2019	91
402	6/02/2019	60
403	7/02/2019	69
404	8/02/2019	91
405	9/02/2019	2
406	10/02/2019	1
407	11/02/2019	41
408	12/02/2019	15
409	13/02/2019	30
410	14/02/2019	81
411	15/02/2019	85
412	16/02/2019	63
413	17/02/2019	2
414	18/02/2019	16
415	19/02/2019	92
416	20/02/2019	43
417	21/02/2019	77
418	22/02/2019	52
419	23/02/2019	97
420	24/02/2019	27
421	25/02/2019	58
422	26/02/2019	52
423	27/02/2019	86
424	28/02/2019	87
425	1/03/2019	30
426	2/03/2019	80
427	3/03/2019	62
428	4/03/2019	49
429	5/03/2019	9
430	6/03/2019	44
431	7/03/2019	89
432	8/03/2019	86
433	9/03/2019	6
434	10/03/2019	66
435	11/03/2019	5
436	12/03/2019	7
437	13/03/2019	7
438	14/03/2019	83
439	15/03/2019	50
440	16/03/2019	20
441	17/03/2019	11
442	18/03/2019	28
443	19/03/2019	10
444	20/03/2019	8
445	21/03/2019	45
446	22/03/2019	11
447	23/03/2019	64
448	24/03/2019	62
449	25/03/2019	46
450	26/03/2019	26
451	27/03/2019	99
452	28/03/2019	98
453	29/03/2019	45
454	30/03/2019	81
455	31/03/2019	99
456	1/04/2019	27
457	2/04/2019	73
458	3/04/2019	20
459	4/04/2019	81
460	5/04/2019	89
461	6/04/2019	96
462	7/04/2019	30
463	8/04/2019	5
464	9/04/2019	3
465	10/04/2019	25
466	11/04/2019	72
467	12/04/2019	42
468	13/04/2019	78
469	14/04/2019	77
470	15/04/2019	24
471	16/04/2019	26
472	17/04/2019	31
473	18/04/2019	83
474	19/04/2019	18
475	20/04/2019	32
476	21/04/2019	87
477	22/04/2019	86
478	23/04/2019	26
479	24/04/2019	80
480	25/04/2019	20
481	26/04/2019	67
482	27/04/2019	8
483	28/04/2019	42
484	29/04/2019	20
485	30/04/2019	6
Sheet1

Cell Formulas
Range	Formula
C1	C1	=MAX(INDEX((MONTH(date)=3)*(YEAR(date)=2018)*Value,0))
B452:B485,B91:B450,B1:B59	B1	=RANDBETWEEN(1,100)
B60:B90	B60	=RANDBETWEEN(1,80)

Named Ranges
Name	Refers To	Cells
date	=Sheet1!$A$1:$A$1461	C1
Value	=Sheet1!$B$1:$B$1461	C1

 

[maabadi]

Change formula at C1 to:

=MAX(INDEX((MONTH(date)=3)*Value,0))

 

[LRATOZ]

Change formula at C1 to:

=MAX(INDEX((MONTH(date)=3)*Value,0))

Wow! What an anticlimax !
It works great!
You saved my day! Thank you so much for that suggestion. I had no idea it was going to be that simple.
Much appreciated maabadi (and of course also Dave Patton).
You are truly Excel experts!

 

[maabadi]

And for finding first date. try this formula with CTRL+SHIFT+ENTER

=INDEX(date,MATCH(1,(MONTH(date)=3)*(Value=C1),0))

 

[Dave Patton]

Try the Aggregate, it may be less resource intensive.

 

[Dave Patton]

Quote The maximum value for March over all the years.

You have the maximum value for March for each year on the Report Sheet; therefore,
you can secure the Max over all years from similar formulas but on the Report Sheet.

T202105a.xlsm
	A	B	C	D	E
1
2	--- Month ---			Max °C	Date
3	January	1	Jan-21	25	3-Jan-21
4	February	2	Feb-21	24	19-Feb-21
5	March	3	Mar-21	29	6-Mar-21
6	April	4	Apr-21	32	5-Apr-21
7	May	5	May-21	28	18-May-21
8	January	13	Jan-22	16	7-Jan-22
9	February	14	Feb-22	27	2-Feb-22
10	March	15	Mar-22	40	5-Mar-22
11
12
13
14
15	By Month
16	January			25	3-Jan-21
17	February			27	2-Feb-22
18	March			40	5-Mar-22
19
Report_a

Cell Formulas
Range	Formula
A3:A10	A3	=TEXT(C3,"mmmm")
B3:B10	B3	=MONTH(C3)+(YEAR(C3)-YEAR($C$3))*12
D3:D10	D3	=INDEX(Data!$D$3:$D$23,MATCH(B3,Data!$B$3:$B$23))
E3:E10	E3	=INDEX(Data!$E$3:$E$23,MATCH(B3,Data!$B$3:$B$23))
D16:D18	D16	=AGGREGATE(14,6,$D$3:$D$10/($A$3:$A$10=A16),1)
E16:E18	E16	=AGGREGATE(14,6,$E$3:$E$10/($D$3:$D$10=D16)*($A$3:$A$10=A16),1)

 

[LRATOZ]

Food for thought Dave.
Thanks for that.
However, in my weather data collection I've got multiple sensors to monitor. So, there will be many formulas to add.
But I see that your solution could be very useful as it will show the extremes for each month and for each year.
I can then add a search function on these max and min values and the result could then be published on the dashboard.
Anyway, I've got lots of experimenting to do
Cheers and all the best!
Luke

 

[LRATOZ]

Could I trouble you one more time please?
Based on the formula as suggested above I now want to find the maximum total value for each month over the years.
Here's a table to explain:

I've written following formula:

=SUM(INDEX((MONTH(Date)=1)*RG203mm,0))

However that returns the sum of rainfall for every January month (Which is 473.3mm).
The result I would like to see is 159.2 as this was the January month with the highest amount of rainfall.
It seems I'm on the right track but I need a little assistance to make this work.
After the result is published I also want to see the month and the year it happened; Is that possible?
Many thanks in advance!
Luke

 

[maabadi]

Why you don't use the only MAX function.
If the January month data is in Row 2 then:

=MAX(B2:N2)