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


J

Jesse

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?
 
Ad

Advertisements

T

T. Valko

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.
 
H

Harlan Grove

Jesse said:
. . . 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:B23>0,B2:B23)))

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

Advertisements

S

Shane Devenshire

Hi,

Since the cells are evenly spaced apart try this array formula:

=AVERAGE(IF(B2:B23*(MOD(ROW(B2:B23),7)=2)<>0,B2:B23,""))

array - press Shift+Ctrl+Enter to enter the formula
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top