Find highest number in a month

[Pofski]

Hi,


the situation is as follows.

In column A, i have a list of dates, spread over a year.

In column B, i have a list of numbers.


How can i find the highest number per month, in such a way that the formula keeps working year after year, and taking into account that sumtimes the highest number for a given month, doesn't change (ia, march has 8 times 374 as highest number, and i need to have just 374 appear next to the cell march)


Thank you in advance.


Oh, and also, I love this forum. You guys have helped me out so much over these last months, there's no way that i could show my gratitude.


Thanks

 

[Faseeh]

Hi Pofski,


If your data is in A1:B10, try this:


DATA

[pre]

2-Jan-12	2
3-Jan-12	3
4-Jan-12	1
5-Feb-12	4
6-Feb-12	5
7-Jan-12	6
8-Jan-12	8
6-Jan-12	6
7-Jan-12	2
8-Jan-12	1[/pre]
=MAX(IF(MONTH(A1)=MONTH($A$1:$A$10),IF(YEAR(A1)=YEAR($A$1:$A$10),$B$1:$B$10)))

Press Ctrl+Shift+Enter to execute, hope that i have understood your problem correctly.


Faseeh

 

[Hui]

=MAX(IF(A2:A100>=(EOMONTH(E1,-1)+1),IF(A2:A100<=EOMONTH(E1,0),B2:B100,FALSE)))Ctrl+Shift+Enter

Where Cell E1 has any date in the month of interest

 

[Pofski]

Fantastic, thanks guys

 

[Sajan]

Hello,

You could simplify the formula slightly as follows:

=MAX((EOMONTH(monthlist+{0},0)=EOMONTH(E1,0))*numlist)

enter with Ctrl + Shift + Enter


E1 is a date in the month of interest

monthlist is the range of months

numlist is the range of numbers corresponding to those months


Cheers,

Sajan.

 

[Colin Legg]

Yet another...

[pre]

=MAX(IF(TEXT(A1:A10,"myy")=TEXT(E1,"myy"),B1:B10))

[/pre]
This is an array formula so must be entered with CTRL+SHIFT+ENTER.