• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find highest number in a month

Pofski

Member
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
 
Hi Pofski,


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


DATA

[pre]
Code:
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
 
=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
 
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.
 
Yet another...

[pre]
Code:
=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.
 
Back
Top