calculate average from non contiguous column, ignoring (0) values
Try this array formula** :
=AVERAGE(IF((MOD(ROW(A2:A23)-ROW(A2),7)=0)*(A2:A23<0),A2:A23))
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
--
Biff
Microsoft Excel MVP
"Jesse" wrote in message
...
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
|