# MAXIF formula

1. ## MAXIF formula

Hi,

I am looking to create a formula (ideally in Excel, but VBA could also work), that would given me the maximum value in column B for the dates between today and the following 45 calendar days.

Column A Column B
January 20, 2015 11.78
January 21, 2015 11.85
January 22, 2015 11.99
January 23, 2015 11.68
January 26, 2015 11.78
January 27, 2015 11.82
...

Not every day will have a set of values as weekends and holidays won't have records. In the example above I assume January 24 and January 25 are weekend days.

Thank you.

2. ## Re: MAXIF formula

Try this array formula Entered with CTRL + SHIFT + ENTER

=MAX(IF((A1:A100 > =TODAY())*(A1:A100 < =TODAY()+45),B1:B100))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

3. ## Re: MAXIF formula

You can use a array formula like this:

=MAX(IF(A2:A100>=TODAY(),IF(A2:A100<=TODAY()+45,B2:B100)))

confirm with CTRL+SHIFT+ENTER

4. ## Re: MAXIF formula

If I get it right use this.
confirm with ctrl+shift+enter

Formula:
`Please Login or Register  to view this content.`

5. ## Re: MAXIF formula

Does this help? This is not an array formula.
``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)