calculating the average of formula results

R

rstask

QuattroPro has a function named @PUREAVG which allows the
user to calculate the average of the results of a
specified column of formulas where only the fulfilled
formulas will be considered in the calculation. When I
use the excel function =average, formulas not fulfilled
are assigned a zero value and are included in the
calculation of the average, both as part of the sum and
part of the count for the divisor. does excell have a
similar function? Although I can accomplish the task using
=IF, I would prefer not.
 
B

Bernie Deitrick

rstask,

If the criteria is that they are greater than zero:
=SUMIF(A:A,">0")/COUNTIF(A:A,">0")
or simply not zero (which requires the actual range):
=SUMIF(A1:A10,"<>0")/COUNTIF(A1:A10,"<>0")

HTH,
Bernie
MS Excel MVP
 
H

Harlan Grove

...
...
or simply not zero (which requires the actual range):
=SUMIF(A1:A10,"<>0")/COUNTIF(A1:A10,"<>0")
Click to expand...
...

If simply not zero, SUM(A1:A10) would give the same result for the numerator.
 
Top