Formulas with-an an array

  • Thread starter Thread starter Dave
  • Start date Start date
D

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
 
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)
 
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)
 
Back
Top