MIN function with a variable range

[TurtleJ]

I would like to use find the minimum of a selected range in a column.
The range with be up to the current row position. The number of rows i
the range is input in another cell. I am looking for a way to construc
the range name that will work with the MIN function. I will copy th
MIN function down to other rows, but the number of rows in the MI
array will remain constant.

Example: In cell F1 is "4", indicating that I want a range 4 ro
long.

I want to calculate the minimum of the indicated number of rows (i
this case, 5) up to the row where the MIN function is. Say mi
function in F10 and want to MIN of column D down to D10. I trie
this:

=MIN("D"&(row(d10)-$F$1)10)

But does not work. My question is how do I construct an arra
designation that will work in the MIN function.

Thanks

 

[JulieD]

HI

try
=MIN(INDIRECT("D"&F1&"10"))

Regard
JulieD

 

[TurtleJ]

Julie:

I probably need some form of the INDEX function. But, I want the D1
to not be fixed so that when I copy the equation down to the next row
it will increment to D11.

Thanks

 

[Debra Dalgleish]

You could use the OFFSET function:

=MIN(OFFSET(D10,-$F$1+1,0,$F$1,1))

 

[TurtleJ]

Thanks, Debra. Works like a champ. Like many things in Excel, I kno
it can be done; I just don't always know how