Using Indirect to create variable MAX formula

I want to create a variable so that I can adjust the number of rows in a MAX formula, and I'm pretty sure the best way is to use a combination of INDIRECT and ROW functions. But I'm not sure how to write it.

I've attached a sample workbook showing a column of numbers in "C". In column "C" I have the "MAX" of the last x rows. (and that formula is copied down, as you'll see). Its currently hard-coded to get the max of the last 30 rows.

In cell E1 I want to use a variable so that I can change the MAX range to whatever I want (for example 10). Advice?


Thanks!

Hi,

I'd use OFFSET. i.e in D30

Formula:

Please Login or Register  to view this content.