Different ranges in formula

[Guest]

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 ?

 

[Domenic]

Try...

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

Hope this helps!

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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)), "")

 

[Guest]

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

 

[Guest]

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 !

 

[Guest]

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