# how to get max of a range x rows above a reference row

1. ## how to get max of a range x rows above a reference row

I've attached a worksheet which illustrates a desired answer for a formula I don't know how to write.

The challenge for me is this: As an example, say that column b has a range of numbers. elsewhere in the sheet I've set a variable, which in this case is 12.

in column C I want to get the max of a range which begins 12 rows up in column B, but goes down 1/2 the variable of 12.

2. ## Re: how to get max of a range x rows above a reference row

Try this...
=MAX(OFFSET(\$B\$1,COUNT(\$B\$1:\$B\$15)-\$F\$1,,\$F\$1/2,1))

3. ## Re: how to get max of a range x rows above a reference row

OR
C13=MAX(OFFSET(B13,-MIN(F1,ROWS(B\$1:B13))+1,,INT(F1/2)))
Try this and copy towards down

4. ## Re: how to get max of a range x rows above a reference row

Thanks. nflsales formula worked for me, possibly because the # of rows in my actual workbook varies, up to 2,000.

5. ## Re: how to get max of a range x rows above a reference row

As long as you got what you needed

Thanks for the feedback!

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