calculate average from non contiguous column, ignoring (0) values
Hi,
Since the cells are evenly spaced apart try this array formula:
=AVERAGE(IF(B2:B23*(MOD(ROW(B2:B23),7)=2)<0,B2:B2 3,""))
array - press Shift+Ctrl+Enter to enter the formula
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"Jesse" wrote:
I'm using Office 2003. I have a need to have 0 in a range of cells (instead
of blank). I need to create an average from some non contiguous cells. I
want the average to ignore cells containing a 0.
I can have the formula | =IF(B2=0,"",B2) | and if b2 is 0 then the formula
returns a blank, and if b2 is < 0 then formula returns value from b2.
If I embed this If formula into an Average formula |
=AVERAGE(IF(B2=0,"",B2),B9,B16,B23) | it returns a #Value error.
How can I create an Average formula that ignores cells with 0 value?
--
Jesse
|