Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Row() formula Help

2 views
Skip to first unread message

JMay

unread,
Jul 18, 2002, 10:55:36 AM7/18/02
to
I use Cell C20 as an Input number , say---> 3

In Cell A16 I have:
=AVERAGE(SMALL(E5:E16,ROW(1:3)))

The "3" in the ROW(1:3) I want to reference in.

so that when I change C20 to say 5
My formula in A16 becomes:

=AVERAGE(SMALL(E5:E16,ROW(1:5)))

How can I do that?

Thanks in Advance,,,

Leo Heuser

unread,
Jul 18, 2002, 1:55:34 PM7/18/02
to
JMay

This array formula will do the job:

=AVERAGE(SMALL(E5:E16,ROW(INDIRECT("1:" & C20))))

To be entered with <Shift><Ctrl><Enter> instead of <Enter>, also
if edited later.

--
Best regards
Leo Heuser
MVP Excel

"JMay" <JM...@cox.net> skrev i en meddelelse
news:IFAZ8.138394$DB.46...@news1.east.cox.net...

JMay

unread,
Jul 18, 2002, 3:16:15 PM7/18/02
to
Thanks Leo!!
It's Much appreciated...
JMay

"Leo Heuser" <leo.h...@get2net.dk> wrote in message
news:O8p78QoLCHA.1976@tkmsftngp11...

Leo Heuser

unread,
Jul 18, 2002, 4:28:38 PM7/18/02
to
You're welcome, Jmay.


"JMay" <JM...@cox.net> skrev i en meddelelse

news:3uEZ8.139610$DB.46...@news1.east.cox.net...

0 new messages