Formulas with-an an array

[Dave]

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

 

[Bob Phillips]

There are many ways, and we probably need the full spec, but you could use
something

=AVERAGE(A1:INDEX(A1:A100,some_condition))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ilia]

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)