calculate average from non contiguous column, ignoring (0) values
 

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

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

calculate average from non contiguous column, ignoring (0) values
 

Jesse wrote...
....
. . . I have a need to have 0 in a range of cells (instead of blank). . . .
....

There could be alternatives.

How can I create an Average formula that ignores cells with 0 value?

But in case there aren't alternatives, the most general way would be

=SUM((B2,B9,B16,B23))/INDEX(FREQUENCY((B2,B9,B16,B23),{0}),2)

If your value cells were always every 7th row beginning with row 2,
you could use an array formula like

=AVERAGE(IF(MOD(ROW(B2:B23),7)=2,IF(B2:B230,B2:B2 3)))

If you don't know what an array formula is, look for it in online Help.

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