Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show actual values added instead of cell references? (=A1+A4 shows as=10+2 in cell) | Excel Discussion (Misc queries) | |||
Show actual values added instead of cell references? (=A1+A4 showsas =10+2 in cell) | Excel Worksheet Functions | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Transferring cell content between workbooks using cell references | Excel Discussion (Misc queries) |