A common way to do this is a defined name formula. You can define a
data column's used range using:

=OFFSET($A$1,0,0,1,COUNTA($A:$A))

So if that's called ColA, you can then say, in a cell formula:

=AVERAGE(ColA)


On Dec 11, 4:29 pm, Dave wrote:
Is there a way to use formulas into an array? For example I have a
array(lets say A1:A100) and in that array there will be values that will be
dynamic as time goes by. so i want a formula that uses this array, but has
another formula in the original formula.

I want to write a formula that looks like this =average(a1:address(b2,b1))
---instead of =average(a1:a100)-----. So that the average formula will
change based on what values are in b1 and b2. The above formula doesn't
work, is there a way to make an array formula incorporate a different
formula? (i was thinking address or index, but can't get either to work)

Thanks