lowest value in range over n rows

[artz]

Hi,

I have a data set of variable length in column G. The first used data row is G8, G7 is a header. I want to find the lowest value in column G over the last n rows where n can be a variable value placed in cell Q2. For example, if the value in cell Q2 were 20, and the last data were in cell G521, we would use the formula: =low(G502:G521). (inclusive of the current cell, i.e., G521.

Could anyone in the Forum help with a cell formula that can return the lowest value over the range of n cells back, with a variable number of rows in column G?

Thanks,

Art

 

[artz]

Hi Aladin,

You nailed it, thanks. The first of the two formulas worked perfectly. I did have trouble with the second. I'll look at it later.

Also, I modified your formula to start at cell J8 and also to check for blanks in column F. The mods that I made are:

=IF(F8<>"",IF(ROWS($J$8:J8)>=$Q$2,MAX(LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,LOOKUP(9.99999999999999E+307,$J$7:J7))),MIN(OFFSET(F8,0,0,-$Q$2))),""),"")

Thanks again,

Art

 

[Aladin Akyurek]

Hi Aladin,

You nailed it, thanks. The first of the two formulas worked perfectly.

Great. Thanks for the feedback.

I did have trouble with the second. I'll look at it later.

The same thing. It just introduces a named constant.

Also, I modified your formula to start at cell J8 and also to check for blanks in column F. The mods that I made are:

=IF(F8<>"",IF(ROWS($J$8:J8)>=$Q$2,
    MAX(LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
      LOOKUP(9.99999999999999E+307,$J$7:J7))),
        MIN(OFFSET(F8,0,0,-$Q$2))),""),"")

Yep...

Thanks again,

Art

You are welcome.