Formulas

[Matt]

i am looking to get an average of a group of cells but I do not want the cell
with a 0 in to be included but the cells numbers will change when I reuse the
sheet. for example a2 will be a 253 cell a3 will be 0 cell a4 will be 432 but
the next day these cell numbers will changeA1 and A@ will have # and A4 will
not and I dont want to change the formuka every day.

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF(A1:A10<>0,A1:A10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Mike]

Try this copy/paste formula into cell A11
=AVERAGEIF(A1:A10,">0")

 

[Harlan Grove]

Try this array formula** :

=AVERAGE(IF(A1:A10<>0,A1:A10))

....

If the range could include negative numbers, zeros probably shouldn't
be excluded. Usually better to use

=AVERAGE(IF(A1:A10>0,A1:A10))

 

[Ashish Mathur]

Hi,

Please array enter (Ctrl+Shift+Enter) the following formula

AVERAGE(IF((range<>0),range))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com