MAX IF AND with criteria ??

Hi all,

I'm having a few issues with the attached spreadsheet, if anyone can assist

I'm trying to get the MAX and MIN movement from a dated range, by doing the following;
for example; the movement between 2 dates i.e. weekly and 2 weekly movement

I'm using the follwing formula- MAX(IF(AND(B2:AA2>B2,B2:AA2<=H2),B13:FZ13))

so the MAX which is greater than 1 Jan 07 but less than 7 Jan 07 and same with the MIN.
then the next one would be between 7 Jan and 14 Jan and so on.. and then for 2 weekly
between 1 Jan & 14 Jan....

Can anyone steer me in the right direction as to why it doesn't work or a more efficient
way to work this out ?

Thanks

Why B:AA in the conditions, but B:FZ in the data range?

Bob's suggestion works if you have valid dates in row 2 but you don't - they are text values, can you change them to dates?

As you have consecutive dates without gaps you could just get the maximum of every 7 columns starting from B13. To do that put this formula in C18 and copy down

=MAX(OFFSET(B$13,0,(ROWS(C$18:C18)-1)*7,1,7))

obviously you can do the same for MIN......and if you want every 14 days just change the two 7s to 14s

Try this version

=MAX(OFFSET($B13,0,(COLUMNS($C18:C18)-1)*7,1,7))