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

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)



"Dave" wrote in message
...
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

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