Max for a given month and year

[MitchellHydro1]

Hey all,

So I have a spreadsheet displaying the highest hydro peak for each day of the year (So there is one value associated to each day of the year). What I am trying to do is run a MAX function to generate a table that associates the maximum daily value of the month with it's appropriate month. I've been tinkering with Max inside If functions but can't seem to nail it down.

Help?

Thanks,

Mitch

[daddylonglegs]

OK, so assuming you have dates in A2:A366 and the max value in B2:B366 then try listing month numbers 1 to 12 in D2:D13 and then use this formula in E2

=MAX(IF(MONTH(A$2:A$366)=D2,B$2:B$366))

confirm with CTRL+SHIFT+ENTER and copy down to E13

[MitchellHydro1]

It didn't work. I have 2 files open, and I need to reference the other file, so the formula won't quite match up.

[FDibbins]

Did you try to adjust the references to point to the other file instead?

And, as pointed out, that is an ARRAY formula, needs to be entered using CTRL SHIFT ENTER, not just enter

[MitchellHydro1]

Yeah, it's the reference to the "d" column that I think is mixing me up. I have the 12 months in the worksheet with my data, not the new worksheet where the MAX values will be displayed. And yes, thank you, I made sure to press CTRL+SHIFT+ENTER.

[FDibbins]

upload samples of both files please

[MitchellHydro1]

Example 1.xlsExample 2.xlsx

So Example 1 is the Data sheet and Example 2 is the sheet that i wish to fill.

[FDibbins]

If your name columns stay in the same order/sequence, try this...
=MAX(IF('[Example 1.xls]Demand'!$A3:$A369=$A2,'[Example 1.xls]Demand'!B$3:B$369))
copied down and across

[MitchellHydro1]

Thanks so much for the help, kinda played around with the ideas that were mentioned and got it to work!

Cheers