Average formula

Posted by Findley Flanagan on November 07, 2001 5:35 AM

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?

Example: 1 2 3 0 5 0 7
THIS WHAT I WANT
(1+2+3+5+7)/5=3.6

NOT THIS
(1+2+3+0+5+0+7)/7=1.71

Posted by Aladin Akyurek on November 07, 2001 5:39 AM

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

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

Aladin

Posted by Barrie Davidson on November 07, 2001 5:40 AM

Assuming your data is in A1:A7, use

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

Regards,
BarrieBarrie Davidson

Posted by Dan on November 07, 2001 5:44 AM

Or try (assuming values in A1 through G1):
=AVERAGE(IF(A1:G1&LT;&GT;0,A1:G1))
and you must press ctrl-shift-enter after entering the formula

Posted by Aladin Akyurek on November 07, 2001 5:54 AM

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

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

Posted by Findley Flanagan on November 07, 2001 5:54 AM

Posted by Dan on November 07, 2001 6:03 AM

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

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):

Posted by Barrie Davidson on November 07, 2001 6:11 AM

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.

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

Posted by Aladin Akyurek on November 07, 2001 7:03 AM

Dan & Barrie --

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,

Posted by Dan on November 07, 2001 7:53 AM

Thanks!

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, ======= : 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. : Aladin, am I correct in my understanding? : Regards,