Find today's date then look at the 5 days prior to find the maximum value

[suprsnipes]

Hi,

I need help with the following formula.

If the cell reference B6 to B92 is equal to today's date I want to find the maximum value in column G for the 5 days prior, so let's say cell B54 is today's date I want to find the maximum value from the 5 days prior which is 5 cells from B53 to B49. How could I do that?

Regards,
suprsnipes

 

[Gerald Higgins]

Can you give us a worked example please ?

I know what you mean by referring to 5 days prior, and I know what you mean by finding the maximum value, but I'm not clear what you want when you talk about both of them at the same time.

 

[suprsnipes]

I would like a formula that can look at column B from rows 6 to 92. When the formula finds that the date in column B matches today's date, today() then look at the equivalent row in column G and find the maximum value over the previous 5 rows of data.

 

[Peter_SSs]

Try this

=MAX(OFFSET(INDEX(G6:G92,MATCH(TODAY(),B6:B92,0)),,,-5))

 

[suprsnipes]

That is perfect thanks for your help guys

Try this

=MAX(OFFSET(INDEX(G6:G92,MATCH(TODAY(),B6:B92,0)),,,-5))

 

[Peter_SSs]

Sorry, mis-read it. I think you need

=MAX(OFFSET(INDEX(G6:G92,MATCH(TODAY(),B6:B92,0)-1),,,-5))


Also, what should happen if today's date is in the top few cells of the column B range so there isn't 5 previous days?