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.

 

[Peter_SSs]

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

? No Marcelo, I only had to read the post carefully myself!

Thanks to all for the help.

You're welcome. Glad we could help.

Unfortunately the only one that worked for me was the one from Petter-SSs.

Obviously you do only need one solution, but both Marcelo's and Phuoc's suggestions work fo me with blank cells for the future months

Post #5

anwaee2.xlsm
	A	B	C	D	E	F								N	O	P	Q
1																Max	Count
2																500	3
3
4		2018	2019	2020	2021	2022								2030
5																Year	Month
6	January	81	393	399	443											2018	March
7	February	92	429	212	500											2021	February
8	March	500	243	64	480											2021	May
9	April	101	466	205	248
10	May	247	309	417	500
11	June	110	358	199	283
12	July	90	470	387	410
13	August	208	197	210	281
14	September	397	172	246
15	October	51	96	395
16	November	393	213	226
17	December	343	324	253
Post #5

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))))

Post #7

anwaee2.xlsm
	A	B	C	D	E	F								N	O	P
1																Max
2																500
3
4		2018	2019	2020	2021	2022								2030
5																Year
6	January	81	393	399	443											Mar 2018
7	February	92	429	212	500											Feb 2021
8	March	500	243	64	480											May 2021
9	April	101	466	205	248
10	May	247	309	417	500
11	June	110	358	199	283
12	July	90	470	387	410
13	August	208	197	210	281
14	September	397	172	246
15	October	51	96	395
16	November	393	213	226
17	December	343	324	253
Post #7

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

 

[Marcelo Branco]

No Marcelo, I only had to read the post carefully myself!

Peter,
I often read the question quickly and as English is not my native language I might miss something

Not this time, I suggested different formulas for year and month because I supposed the months in column A could be texts, as we often see in questions around here, not dates

Looks like I was right - Phuoc's formula that looks perfect didn't work.

M.

 

[Peter_SSs]

I supposed the months in column A could be texts, as we often see in questions around here, not dates

Looks like I was right - Phuoc's formula that looks perfect didn't work.

Hi Marcelo
In the final mini-sheet of post #11 I have text months in column A and Phuoc's formula in column P - and it is working.
If it does not work for you and/or the OP, could it be a version issue?

Also, if your formula allows for text months, why do you think the OP is reporting that your formula did not work either?

 

[Marcelo Branco]

In the final mini-sheet of post #11 I have text months in column A and Phuoc's formula in column P - and it is working

Peter,

Insert a space before either before February or March or May...

What I'm trying to say is that I don't think it's safe to assume that the texts in column A can be used in formulas - it's often imported data and a single extraneous character can make the formula fail.

M.

 

[Peter_SSs]

I take your point Marcelo, but I don't think it unreasonable (to start with at least) to take the OP's information at face value.

I have the months of the year in A6 thru A17

In any case, that still does not account for why the OP says my formulas work and yours do not.

If there could be leading (or trailing) spaces around the month names, Phuoc's formula could have this minor tweak & still produce the result in a single cell as requested.
=IFERROR(AGGREGATE(15,6,(1&$A$6:$A$17&$B$4:$N$4)/($B$6:$N$17=$P$2),ROWS($P$6:P6)),"")

 

[Marcelo Branco]

In any case, that still does not account for why the OP says my formulas work and yours do not.

I don't have an explanation for this, but as it looks like OP has already found a solution (that's what matters to him) why my formula didn't work has become irrelevant.

I was just explaining why I suggested two different formulas - one for the month and one for the year - but this is now just a technical curiosity, as the problem is solved.

M.

 

[anwaee2]

Maybe it should be noted that I am on a Mac with Excel for Mac 2011. I know I have had other issues that does not happen with IBM compatible Excel.
And thanks again to all as my problem was solved.

 

[anwaee2]

Just to be on the safe side, I tried both of these again twiice and still got an empty/blank cell. Maybe it is time I upgraded to versions 2016 or 2019.

Peter_SSs :
=IFERROR(AGGREGATE(15,6,(1&$A$6:$A$17&$B$4:$N$4)/($B$6:$N$17=$P$2),ROWS($P$6:P6)),"")

Marcelo Branco:
=IFERROR(AGGREGATE(15,6,($A$6:$A$17&$B$4:$N$4)/($B$6:$N$17=$P$2),ROWS($P$6:P6)),"")

 

[Marcelo Branco]

Marcelo Branco:
=IFERROR(AGGREGATE(15,6,($A$6:$A$17&$B$4:$N$4)/($B$6:$N$17=$P$2),ROWS($P$6:P6)),"")

Something is wrong here... I did not suggest this formula.

As I've said, I don't think it's safe to use column A in a formula as it might have text containing extraneous characters - most likely this is the reason the formula didn't work.

I suggested two different formulas (see post #5), one for the year and one for the month.

M.