removing zero values when averaging

  • Thread starter Thread starter Tim
  • Start date Start date
T

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.
 
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!
 
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, B1:D10= where entering cells.

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

Hope it helps.

-Bo
 
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
 
Tim:

Your first instinct might have been right: get rid of the zeroes.

Select the data area (ctrl +*)
edit|find 0
replace with [just leave it empty]
replace all.

Could be quicker, too!

Al
 
Back
Top