Cell references
 

I have the following codes in my worksheet

=average(range) (1)

and I would like to replace them with

{=average(if(range<0,range,""))}. (2)

How do I extract the range from (1) so that I input it in (2)?

Basically, I am substituting a regular average funcion with an array formula
that averages by excluding zero values in the range.

Thansk,
Mira

Cell references
 

see if this helps
ActiveCell.FormulaArray = "=AVERAGE(IF(L1:L3<0,L1:L3))"
--
Don Guillett
SalesAid Software

"referencing ranges in formulas" <referencing ranges in
wrote in message
...
I have the following codes in my worksheet

=average(range) (1)

and I would like to replace them with

{=average(if(range<0,range,""))}. (2)

How do I extract the range from (1) so that I input it in (2)?

Basically, I am substituting a regular average funcion with an array
formula
that averages by excluding zero values in the range.

Thansk,
Mira

Cell references
 

Thank you very much, Don.

What do you think might be the best way to extract the already speicified
range in the first formula? Is there an easy way to do that or should I just
parse through the formula text to get it?

"Don Guillett" wrote:

see if this helps
ActiveCell.FormulaArray = "=AVERAGE(IF(L1:L3<0,L1:L3))"
--
Don Guillett
SalesAid Software

"referencing ranges in formulas" <referencing ranges in
wrote in message
...
I have the following codes in my worksheet

=average(range) (1)

and I would like to replace them with

{=average(if(range<0,range,""))}. (2)

How do I extract the range from (1) so that I input it in (2)?

Basically, I am substituting a regular average funcion with an array
formula
that averages by excluding zero values in the range.

Thansk,
Mira