AVERAGE FUNCTION

[Terri]

I need to calculate the Average of various cells in a
consolidated worksheet BUT need to ignore zero values as
they will influence the true answer(by dividing by the
amount of cells instead if the amount of cells with a
value in). I found a way to ignore zero values when the
info is for example from A1 to A5 but not when the info is
on other worksheets. I have used Average, Average and If
combined as well as AVERAGEA but in all instances zero
factors gets taken into consideration.

PS: There is a worksheet for every division which in turn
is linked into my main data sheet which has a worksheet
for every division as well as a consolidated spreadsheet
for all the division combined.

 

[David J. Braden]

One solution:
=SUM(rng)/COUNTIF(rng,"<>0")

HTH
Dave Braden