how to easily vary (parametrize) array sizes in Excel?

[carolyn]

Suppose I have a column of numerical data, say, in Column A.

I want to compute average, e.g., average(A1:A1000).
But sometimes I want to compute average(A23:A1000) or
average(A48:A1000).

Is it possible for me to input the bottom of the range, say "A36", in
another cell, such as C1, and then compute
average(whatever cell is specified in cell C1,A1000)?

How do I do this simply?
MD

 

[Guest]

=AVERAGE(INDIRECT(C1):A1000)

 

[mensanator]

Suppose I have a column of numerical data, say, in Column A.

I want to compute average, e.g., average(A1:A1000).
But sometimes I want to compute average(A23:A1000) or
average(A48:A1000).

Is it possible for me to input the bottom of the range, say "A36", in
another cell, such as C1, and then compute
average(whatever cell is specified in cell C1,A1000)?

How do I do this simply?

If you have this in column A...

1
2
3
4
5
6
7
8
9
10


Full

=ADDRESS(1,1,1,1)
=ADDRESS(10,1,1,1)
=AVERAGE(INDIRECT(A15,1):INDIRECT(A16,1))


Partial
6
=ADDRESS(A21,1,1,1)
=ADDRESS(10,1,1,1)
=AVERAGE(INDIRECT(A22,1):INDIRECT(A23,1))

....you'll see this:

1
2
3
4
5
6
7
8
9
10


Full

$A$1
$A$10
5.5


Partial
6
$A$6
$A$10
8

 

[Roger Govier]

Hi Carolyn

One way
=AVERAGE(INDEX(A:A,C1):A1000)