Archive of Mr Excel Message Board

Assuming that your numbers are in A1:A7 (otherwise adjust):

=SUMIF(A1:A7,">0")/MAX(1,COUNTIF(A1:A7,">0"))

Aladin

Assuming your data is in A1:A7, use

=SUMIF(A1:A7,">0",A1:A7)/COUNTIF(A1:A7,">0")

Regards,
BarrieBarrie Davidson

Dan --

I'd still go for a non-array formula:

=SUMIF(A1:G1,">0")/MAX(1,COUNTIF(A1:G1,">0"))

for reasons of efficiency. This version picks out numbers "greater than zero" to average.

Regards,

Aladin

=============

I am a novice at Excel and need what is probably a simple formula. Can anyone out there show me how to write a formula that will average all numbers in a range that are greater than zero(0)while disregarding the zeroes as part of that average?

By the way, I didn't realize that the original post stated "greater than 0", I was thinking "non-zero" numbers. The correct array formula should be :

=AVERAGE(IF(A1:G1>0,A1:G1)) : Or try (assuming values in A1 through G1):

Aladin, am I correct in my understanding?

Regards,
Barrie Please explain what you mean by efficiency. Calculation efficiency or user efficiency? Is it generally better to avoid array formulas?

Barrie Davidson

Barrie is right. I had calculation (or computational) efficiency in mind, especially with large sets of data. I also believe that array formulas including ones with SUMPRODUCT or FREQUENCY are harder to grasp (take time to understand them). So maybe we have here a bit user efficiency issue here too. Rewriting an AVERAGE formula in terms of SUM and COUNT or, in this particular case SUMIF and COUNTIF is a lot easier than constructing array or SUMPRODUCT formulas with Boolean terms. For the record, I'm not opposing using array or inherently array formulas. I believe I wouldn't fit the bill.

Regards,

Aladin

======= My understanding of array formulas is that each time one member of the array is changed, the entire array is re-evaluated. Not really a big deal if you are working with a small data set. So it is a matter of calculation efficiency. , am I correct in my understanding? Regards,