removing zero values when averaging

[Tim]

Is there any way to remove zero values (ie, make them a
null value) from an average in either Excel 97 or XP?

I have a large spreadsheet with many zero values, but want
to disregard these as entries, without going through the
entire spreadsheet and deleting them.

Tim.

 

[Fenco]

To embed your formula in function IF: For example your orignal figures is cell A1= 45, cell B1 = 0, then using functiion IF like that:-
=IF(B1=0,"", A1/B1)

Hope can help you!

 

[beeawwb]

I don't know that much about advanced Excel functions, but could you d
something like...

=(SUMIF(Range:Range,"<>0"))/(COUNTIF(Range:Range,">0"))

That works. Just tried it.

Example.

A1 = formula, B110= where entering cells.

=(SUMIF(B110,"<>0"))/(COUNTIF(B110,">0"))

Hope it helps.

-Bo

 

[Bif]

Hi Tim,

Here's one way:

Say you want to average the values in the range A1:A8 but
exclude zero values.

=AVERAGE(IF(A1:A8,A1:A8))

Entered as an array - CTRL+SHIFT+ENTER

Biff