Different ranges in formula
 

Hi,
This is probably an easy one - but I don't feel the sharpest right now...
I have a MIN-formula like this:
=MIN(E4;E5;E6;E7;E8;E9;E10;E11)
but I'd like it to go as many rows down depending on a cell A1 (=8 in this
case).
E.g. if A1=1 the formula should read
= MIN(E4)
E.g. if A1=3 the formula should read
= MIN(E4;E5;E6)
etc. etc.
Any help appreciated, I tried the Offset-function but couldn't get around
it. Or am I totally off-track here ?

Different ranges in formula
 

Try...

=MIN(E4:INDEX(E4:E65536,A1))

Hope this helps!

In article ,
Jan wrote:

Hi,
This is probably an easy one - but I don't feel the sharpest right now...
I have a MIN-formula like this:
=MIN(E4;E5;E6;E7;E8;E9;E10;E11)
but I'd like it to go as many rows down depending on a cell A1 (=8 in this
case).
E.g. if A1=1 the formula should read
= MIN(E4)
E.g. if A1=3 the formula should read
= MIN(E4;E5;E6)
etc. etc.
Any help appreciated, I tried the Offset-function but couldn't get around
it. Or am I totally off-track here ?

Different ranges in formula
 

=MIN(OFFSET(E4,0,0,A1,1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jan" wrote in message
...
Hi,
This is probably an easy one - but I don't feel the sharpest right now...
I have a MIN-formula like this:
=MIN(E4;E5;E6;E7;E8;E9;E10;E11)
but I'd like it to go as many rows down depending on a cell A1 (=8 in this
case).
E.g. if A1=1 the formula should read
= MIN(E4)
E.g. if A1=3 the formula should read
= MIN(E4;E5;E6)
etc. etc.
Any help appreciated, I tried the Offset-function but couldn't get around
it. Or am I totally off-track here ?

Different ranges in formula
 

One way,

=IF(LEN(A1), MIN(E4:INDIRECT("e"&3+A1)), "")

On second thought, this might be better:

=IF(COUNT(A1), MIN(E4:INDIRECT("e"&3+A1)), "")

--
Kevin Vaughn


"Jan" wrote:

Hi,
This is probably an easy one - but I don't feel the sharpest right now...
I have a MIN-formula like this:
=MIN(E4;E5;E6;E7;E8;E9;E10;E11)
but I'd like it to go as many rows down depending on a cell A1 (=8 in this
case).
E.g. if A1=1 the formula should read
= MIN(E4)
E.g. if A1=3 the formula should read
= MIN(E4;E5;E6)
etc. etc.
Any help appreciated, I tried the Offset-function but couldn't get around
it. Or am I totally off-track here ?

Different ranges in formula
 

Don't use this one. indirect is volatile and since you have 2 responses that
are not volatile, they should definitely be preferred over ... wait is offset
volatile? Actually, offset is volatile, and if a volatile answer is good
enough for Bob Phillips, who am I to disagree :)
--
Kevin Vaughn


"Kevin Vaughn" wrote:

One way,

=IF(LEN(A1), MIN(E4:INDIRECT("e"&3+A1)), "")

On second thought, this might be better:

=IF(COUNT(A1), MIN(E4:INDIRECT("e"&3+A1)), "")

--
Kevin Vaughn


"Jan" wrote:

Hi,
This is probably an easy one - but I don't feel the sharpest right now...
I have a MIN-formula like this:
=MIN(E4;E5;E6;E7;E8;E9;E10;E11)
but I'd like it to go as many rows down depending on a cell A1 (=8 in this
case).
E.g. if A1=1 the formula should read
= MIN(E4)
E.g. if A1=3 the formula should read
= MIN(E4;E5;E6)
etc. etc.
Any help appreciated, I tried the Offset-function but couldn't get around
it. Or am I totally off-track here ?

Different ranges in formula
 

Absolutely !
I run Excel 2000, so maybe that's why I had to make a small modification:
=MIN(E4:INDEX(E4:E65536;H1))
(replace , with ; )

Thanks !

"Domenic" wrote:

Try...

=MIN(E4:INDEX(E4:E65536,A1))

Hope this helps!

In article ,
Jan wrote:

Hi,
This is probably an easy one - but I don't feel the sharpest right now...
I have a MIN-formula like this:
=MIN(E4;E5;E6;E7;E8;E9;E10;E11)
but I'd like it to go as many rows down depending on a cell A1 (=8 in this
case).
E.g. if A1=1 the formula should read
= MIN(E4)
E.g. if A1=3 the formula should read
= MIN(E4;E5;E6)
etc. etc.
Any help appreciated, I tried the Offset-function but couldn't get around
it. Or am I totally off-track here ?

Different ranges in formula
 

Actually, that's a regional issue (difference between using ; and , as
delimiters)
--
Kevin Vaughn


"Jan" wrote:

Absolutely !
I run Excel 2000, so maybe that's why I had to make a small modification:
=MIN(E4:INDEX(E4:E65536;H1))
(replace , with ; )

Thanks !

"Domenic" wrote:

Try...

=MIN(E4:INDEX(E4:E65536,A1))

Hope this helps!

In article ,
Jan wrote:

Hi,
This is probably an easy one - but I don't feel the sharpest right now...
I have a MIN-formula like this:
=MIN(E4;E5;E6;E7;E8;E9;E10;E11)
but I'd like it to go as many rows down depending on a cell A1 (=8 in this
case).
E.g. if A1=1 the formula should read
= MIN(E4)
E.g. if A1=3 the formula should read
= MIN(E4;E5;E6)
etc. etc.
Any help appreciated, I tried the Offset-function but couldn't get around
it. Or am I totally off-track here ?