Find the month and year of the highest number in a range.

[anwaee2]

I have the months of the year in A6 thru A17 on the "Summary" worksheet.

I have the years 2018 thru 2030 in the cells B4 thru N4 on the "Summary" worksheet.

I have the total monthly rainfall in cells B6 thru N17 in their respective cells according to month and year on the "Summary" worksheet.

I have the highest monthly rainfall amount in cell AA39 on the "Data" worksheet with the formula: =IFERROR(MAX(Summary!B6:N17),"")

I would like to have the month and year of the highest monthly rainfall in another cell such as Z39 on the "Data" worksheet.



Is there a way of doing this?

Thanks in advance for any help I may get on this. And thanks again for all the help in the past.

 

[Marcelo Branco]

I have the highest monthly rainfall amount in cell AA39 on the "Data" worksheet with the formula: =IFERROR(MAX(Summary!B6:N17),"")

To get the year
=INDEX(Summary!B4:N4,AGGREGATE(15,6,(COLUMN(Summary!B4:N4)-COLUMN(Summary!B4)+1)/(Summary!B6:N17=AA39),1))

To get the month
=INDEX(Summary!A6:A17,AGGREGATE(15,6,(ROW(Summary!A6:A17)-ROW(Summary!A6)+1)/(Summary!B6:N17=AA39),1))

M.

 

[Peter_SSs]

I would have thought it possible that the maximum highest monthly rainfall could occur more than once in 13 years.
If that were to happen, what result would you want?

As it stands, Marcelo's formulas work fine if it is a single maximum but may produce a completely wrong result if that maximum did occur more than once. I have condensed it down to a single sheet here to demonstrate.

anwaee2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
4		2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030			Max
5																	500
6	January	81	393	399	443	67	441	6	382	429	184	189	424	395
7	February	92	429	212	226	138	440	152	102	495	500	340	278	361
8	March	12	243	64	480	217	23	83	103	445	274	419	154	190		February	2024
9	April	101	466	205	248	221	399	49	205	434	375	128	104	267
10	May	247	309	417	475	318	150	117	120	220	193	495	314	396
11	June	110	358	199	283	277	396	60	148	314	196	411	380	76
12	July	90	470	387	410	16	179	148	470	421	430	193	38	472
13	August	208	197	210	281	258	213	500	33	459	434	55	105	217
14	September	397	172	246	471	155	359	368	282	361	314	102	303	4
15	October	51	96	395	131	37	424	217	388	273	346	472	350	158
16	November	393	213	226	0	152	231	457	196	210	313	428	267	244
17	December	343	324	253	46	336	335	337	306	32	136	275	50	61
Summary

Cell Formulas
Range	Formula
Q5	Q5	=IFERROR(MAX(B6:N17),"")
P8	P8	=INDEX(A6:A17,AGGREGATE(15,6,(ROW(A6:A17)-ROW(A6)+1)/(B6:N17=Q5),1))
Q8	Q8	=INDEX(B4:N4,AGGREGATE(15,6,(COLUMN(B4:N4)-COLUMN(B4)+1)/(B6:N17=Q5),1))

Perhaps even something like this?

anwaee2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
4		2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030			Max	Count
5																	500	2
6	January	81	393	399	443	67	441	6	382	429	184	189	424	395
7	February	92	429	212	226	138	440	152	102	495	500	340	278	361
8	March	12	243	64	480	217	23	83	103	445	274	419	154	190		Multiple	Multiple
9	April	101	466	205	248	221	399	49	205	434	375	128	104	267
10	May	247	309	417	475	318	150	117	120	220	193	495	314	396
11	June	110	358	199	283	277	396	60	148	314	196	411	380	76
12	July	90	470	387	410	16	179	148	470	421	430	193	38	472
13	August	208	197	210	281	258	213	500	33	459	434	55	105	217
14	September	397	172	246	471	155	359	368	282	361	314	102	303	4
15	October	51	96	395	131	37	424	217	388	273	346	472	350	158
16	November	393	213	226	0	152	231	457	196	210	313	428	267	244
17	December	343	324	253	46	336	335	337	306	32	136	275	50	61
Summary

Cell Formulas
Range	Formula
Q5	Q5	=IFERROR(MAX(B6:N17),"")
R5	R5	=COUNTIF(B6:N17,Q5)
P8	P8	=IF(R5>1,"Multiple",INDEX(A6:A17,AGGREGATE(15,6,(ROW(A6:A17)-ROW(A6)+1)/(B6:N17=Q5),1)))
Q8	Q8	=IF(R5>1,"Multiple",INDEX(B4:N4,AGGREGATE(15,6,(COLUMN(B4:N4)-COLUMN(B4)+1)/(B6:N17=Q5),1)))

 

[Peter_SSs]

Perhaps even something like this?

.. or this?

anwaee2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
4		2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030			Max
5																	500
6	January	81	393	399	443	67	441	6	382	429	184	189	424	395
7	February	92	429	500	226	138	440	152	102	495	500	340	278	361		Month	Year
8	March	12	243	64	480	217	23	83	103	445	274	419	154	190		February	2020		2	3
9	April	101	466	205	248	221	399	49	205	434	375	128	104	267		August	2024		8	7
10	May	247	309	417	475	318	150	117	120	220	193	495	314	396		February	2027		2	10
11	June	110	358	199	283	277	396	60	148	314	196	411	380	76
12	July	90	470	387	410	16	179	148	470	421	430	193	38	472
13	August	208	197	210	281	258	213	500	33	459	434	55	105	217
14	September	397	172	246	471	155	359	368	282	361	314	102	303	4
15	October	51	96	395	131	37	424	217	388	273	346	472	350	158
16	November	393	213	226	0	152	231	457	196	210	313	428	267	244
17	December	343	324	253	46	336	335	337	306	32	136	275	50	61
Summary

Cell Formulas
Range	Formula
Q5	Q5	=IFERROR(MAX(B6:N17),"")
P8:P14	P8	=IF(S8="","",INDEX(A$6:A$17,S8))
Q8:Q14	Q8	=IF(T8="","",INDEX(B$4:N$4,T8))
S8:S14	S8	=IF(T8="","",AGGREGATE(15,6,(ROW(B$6:N$17)-ROW(B$6)+1)/((B$6:N$17=Q$5)*(COLUMN(B$6:N$17)-COLUMN(B$6)+1=T8)),COUNTIF(T$8:T8,T8)))
T8:T14	T8	=IFERROR(AGGREGATE(15,6,(COLUMN(B$6:N$17)-COLUMN(B$6)+1)/(B$6:N$17=Q$5),ROWS(Y$8:Y8)),"")

 

[Marcelo Branco]

Formulas to deal with multiple Maximums

Pasta1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1																Max	Count
2																500	7
3
4		2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030
5																Year	Month
6	January	81	393	399	443	67	441	6	382	429	184	189	500	395		2018	March
7	February	92	429	212	500	138	440	152	102	495	500	340	278	361		2021	February
8	March	500	243	64	480	217	23	500	103	445	274	419	154	190		2021	May
9	April	101	466	205	248	221	399	49	205	434	375	128	104	267		2024	March
10	May	247	309	417	500	318	150	117	120	220	193	495	314	396		2024	August
11	June	110	358	199	283	277	396	60	148	314	196	411	380	76		2027	February
12	July	90	470	387	410	16	179	148	470	421	430	193	38	472		2029	January
13	August	208	197	210	281	258	213	500	33	459	434	55	105	217
14	September	397	172	246	471	155	359	368	282	361	314	102	303	4
15	October	51	96	395	131	37	424	217	388	273	346	472	350	158
16	November	393	213	226	0	152	231	457	196	210	313	428	267	244
17	December	343	324	253	46	336	335	337	306	32	136	275	50	61
Plan7

Cell Formulas
Range	Formula
P2	P2	=MAX(B6:N17)
Q2	Q2	=COUNTIF(B6:N17,P2)
P6:P13	P6	=IF(ROWS(P$6:P6)>$Q$2,"",INDEX(B$4:N$4,AGGREGATE(15,6,(COLUMN(B$4:N$4)-COLUMN(B$4)+1)/(B$6:N$17=$P$2),ROWS(P$6:P6))))
Q6:Q13	Q6	=IF(P6="","",INDEX(A$6:A$17,AGGREGATE(15,6,(ROW(A$6:A$17)-ROW(A$6)+1)/(INDEX($B$6:$N$17,0,MATCH(P6,B$4:N$4,0))=$P$2),COUNTIF(P$6:P6,P6))))

M.

 

[Peter_SSs]

Formulas to deal with multiple Maximums

.. and some more.

anwaee2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1																Max	Count
2																500	7
3
4		2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030
5																Year	Month
6	January	81	393	399	443	67	441	6	382	429	184	189	500	395		2018	March
7	February	92	429	212	500	138	440	152	102	495	500	340	278	361		2021	February
8	March	500	243	64	480	217	23	500	103	445	274	419	154	190		2021	May
9	April	101	466	205	248	221	399	49	205	434	375	128	104	267		2024	March
10	May	247	309	417	500	318	150	117	120	220	193	495	314	396		2024	August
11	June	110	358	199	283	277	396	60	148	314	196	411	380	76		2027	February
12	July	90	470	387	410	16	179	148	470	421	430	193	38	472		2029	January
13	August	208	197	210	281	258	213	500	33	459	434	55	105	217
14	September	397	172	246	471	155	359	368	282	361	314	102	303	4
15	October	51	96	395	131	37	424	217	388	273	346	472	350	158
16	November	393	213	226	0	152	231	457	196	210	313	428	267	244
17	December	343	324	253	46	336	335	337	306	32	136	275	50	61
Sheet2

Cell Formulas
Range	Formula
P2	P2	=MAX(B6:N17)
Q2	Q2	=COUNTIF(B6:N17,P2)
P6:P13	P6	=IF(ROWS(T$6:T6)>Q$2,"",AGGREGATE(15,6,B$4:N$4/(B$6:N$17=P$2),ROWS(P$6:P6)))
Q6:Q13	Q6	=IF(P6="","",INDEX(A:A,AGGREGATE(15,6,ROW(A$6:A$17)/(INDEX(B$6:N$17,0,P6-B$4+1)=P$2),COUNTIF(P$6:P6,P6))))

 

[Phuoc]

Another way:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1																Max
2																500
3
4		2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030
5																Month&Year
6	January	81	393	399	443	67	441	6	382	429	184	189	500	395		Mar 2018
7	February	92	429	212	500	138	440	152	102	495	500	340	278	361		Feb 2021
8	March	500	243	64	480	217	23	500	103	445	274	419	154	190		May 2021
9	April	101	466	205	248	221	399	49	205	434	375	128	104	267		Mar 2024
10	May	247	309	417	500	318	150	117	120	220	193	495	314	396		Aug 2024
11	June	110	358	199	283	277	396	60	148	314	196	411	380	76		Feb 2027
12	July	90	470	387	410	16	179	148	470	421	430	193	38	472		Jan 2029
13	August	208	197	210	281	258	213	500	33	459	434	55	105	217
14	September	397	172	246	471	155	359	368	282	361	314	102	303	4
15	October	51	96	395	131	37	424	217	388	273	346	472	350	158
16	November	393	213	226	0	152	231	457	196	210	313	428	267	244
17	December	343	324	253	46	336	335	337	306	32	136	275	50	61
Sheet1

Cell Formulas
Range	Formula
P2	P2	=MAX(B6:N17)
P6:P16	P6	=IFERROR(AGGREGATE(15,6,($A$6:$A$17&$B$4:$N$4)/($B$6:$N$17=$P$2),ROWS($P$6:P6)),"")

 

[Peter_SSs]

Another way:

I had followed Marcelo's method of month/year results in separate cells, but re-reading post #1, I think you are right on in producing both in a single cell.

 

[Marcelo Branco]

I had followed Marcelo's method of month/year results in separate cells,

Sorry Peter, if I have misled you into such a serious error... it was not my intention...

M.

 

[anwaee2]

Thanks to all for the help. Unfortunately the only one that worked for me was the one from Petter-SSs. I converted it to look like the result that Phuoc got. The others either gave me a blank cell or a #Num! error for the month, the year was fine. I wonder if that was because I have a lot (mostly) of empty cells since it is not 2022-2030 yet. Thanks again to all, the Mr. Excel board is the best.