software

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 ?
Try...

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

Hope this helps!

In article gt;,
Jan gt; wrote:

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

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

=IF(LEN(A1), MIN(E4:INDIRECT(quot;equot;amp;3 A1)), quot;quot;)

On second thought, this might be better:

=IF(COUNT(A1), MIN(E4:INDIRECT(quot;equot;amp;3 A1)), quot;quot;)

--
Kevin Vaughnquot;Janquot; wrote:

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

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 Vaughnquot;Kevin Vaughnquot; wrote:

gt; One way,
gt;
gt; =IF(LEN(A1), MIN(E4:INDIRECT(quot;equot;amp;3 A1)), quot;quot;)
gt;
gt; On second thought, this might be better:
gt;
gt; =IF(COUNT(A1), MIN(E4:INDIRECT(quot;equot;amp;3 A1)), quot;quot;)
gt;
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;Janquot; wrote:
gt;
gt; gt; Hi,
gt; gt; This is probably an easy one - but I don't feel the sharpest right now...
gt; gt; I have a MIN-formula like this:
gt; gt; =MIN(E4;E5;E6;E7;E8;E9;E10;E11)
gt; gt; but I'd like it to go as many rows down depending on a cell A1 (=8 in this
gt; gt; case).
gt; gt; E.g. if A1=1 the formula should read
gt; gt; = MIN(E4)
gt; gt; E.g. if A1=3 the formula should read
gt; gt; = MIN(E4;E5;E6)
gt; gt; etc. etc.
gt; gt; Any help appreciated, I tried the Offset-function but couldn't get around
gt; gt; it. Or am I totally off-track here ?
gt; gt;
gt; gt;

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 !

quot;Domenicquot; wrote:

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

Actually, that's a regional issue (difference between using ; and , as
delimiters)
--
Kevin Vaughnquot;Janquot; wrote:

gt; Absolutely !
gt; I run Excel 2000, so maybe that's why I had to make a small modification:
gt; =MIN(E4:INDEX(E4:E65536;H1))
gt; (replace , with ; )
gt;
gt; Thanks !
gt;
gt; quot;Domenicquot; wrote:
gt;
gt; gt; Try...
gt; gt;
gt; gt; =MIN(E4:INDEX(E4:E65536,A1))
gt; gt;
gt; gt; Hope this helps!
gt; gt;
gt; gt; In article gt;,
gt; gt; Jan gt; wrote:
gt; gt;
gt; gt; gt; Hi,
gt; gt; gt; This is probably an easy one - but I don't feel the sharpest right now...
gt; gt; gt; I have a MIN-formula like this:
gt; gt; gt; =MIN(E4;E5;E6;E7;E8;E9;E10;E11)
gt; gt; gt; but I'd like it to go as many rows down depending on a cell A1 (=8 in this
gt; gt; gt; case).
gt; gt; gt; E.g. if A1=1 the formula should read
gt; gt; gt; = MIN(E4)
gt; gt; gt; E.g. if A1=3 the formula should read
gt; gt; gt; = MIN(E4;E5;E6)
gt; gt; gt; etc. etc.
gt; gt; gt; Any help appreciated, I tried the Offset-function but couldn't get around
gt; gt; gt; it. Or am I totally off-track here ?
gt; gt;