1

Good morning,

I'm using Excel 2016 to analyze stock data. I need to find the maximum value for a data range within a specific month. I know how to do this using several helper columns, but I'd like to take advantage of the "maxifs" function. I want the maximum value of the previous month to be calculated on the first date of the next month. For example, on 9/1/2010, I would like the maximum value of August 2010 to be displayed. I need the code to be dynamic so I can use any data set.

Here's an example of my code from cell C24:

=IF(MONTH($A24)<>MONTH($A23),MAXIFS(B$2:B23,A$2:A23,MONTH(A$2:A23)=MONTH(A23)),"-")

And here's a screen shot of my example spreadsheet to give some context:

Example Spreadsheet

I know I have something slightly off on the criteria1 syntax. Any help would be appreciated.

Thanks!

Community
  • 1
  • 1
Sescopeland
  • 315
  • 2
  • 16

1 Answers1

2

You would bracket the month:

=MAXIFS(B$2:B23,A$2:A23,">=" & EOMONTH(A23,-1) +1,A$2:A23,"<" & EOMONTH(A23,0)+1)

So your whole formula:

=IF(MONTH($A24)<>MONTH($A23),MAXIFS(B$2:B23,A$2:A23,">=" & EOMONTH(A23,-1) +1,A$2:A23,"<" & EOMONTH(A23,0)+1),"-")
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    Good call with `EOMONTH()`, I always forget about that. – BruceWayne Apr 28 '17 at 15:09
  • @Sescopeland in a few minutes you will be able to mark this as correct, Please do by clicking the check mark by the answer. – Scott Craner Apr 28 '17 at 15:13
  • 1
    I typically use `"<"&EOMONTH(A23, 0)+1` since 08:00am on the last day is greater then EOMONTH(A23, 0); i.e. any time value will negate `"<="&EOMONTH(A23,0)` so I like *less than midnight of the first day of next month*. –  Apr 28 '17 at 15:36
  • @Jeeped then wouldn't you want to do the same type of thing on the front end `">=" & EOMONTH(A23,-1) +1`? Becuase any time will be greater than the first way I had it `">" & EOMONTH(A23,-1) `. – Scott Craner Apr 28 '17 at 15:41
  • Yes, that would be appropriate under the same circumstances of date/times instead of dates. –  Apr 28 '17 at 15:59