Average: 2nd argument as a variable in another cell av(c6:c(a3))

[Tony the Tiger]

I bring in lists of various lengths to be averaged. My averaging forumlas are
already set up on the page. If the list is shorter than my predetermined
column length, I get errors of DIV by zero. If they are longer, then not all
are included.
I want to count the length of the list and use the result as a variable in
my averaging formula so the column length is always the same as the list I
bring in. For example:
AV(c6:c(a3)) where 'a3' is the count result of my list. The error I get is
the #NAME? error.
I am using Excel 2003.

 

[pdberger]

Tony --
I'm confident someone else will present something more elegant. In the
meantime, here's a 'brute force' approach

A B
1 1 =COUNT(A:A)
2 2 =ADDRESS(B1,A1)
3 3 =AVERAGE(A1:INDIRECT(B2))
4 4
5 5

The result in B1 is 5
B2 $A$5
B3 3

Modify to fit.

HTH

 

[Jim Cone]

TtT,

=AVERAGE(OFFSET(C6,0,0,A3,1))
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Tony the Tiger"
<Tony the (e-mail address removed)>
wrote in message
I bring in lists of various lengths to be averaged. My averaging forumlas are
already set up on the page. If the list is shorter than my predetermined
column length, I get errors of DIV by zero. If they are longer, then not all
are included.
I want to count the length of the list and use the result as a variable in
my averaging formula so the column length is always the same as the list I
bring in. For example:
AV(c6:c(a3)) where 'a3' is the count result of my list. The error I get is
the #NAME? error.
I am using Excel 2003.