Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

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

Re: Average formula

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

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

Re: Average formula

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

Re: Average formula

Posted by Dan on November 07, 2001 5:44 AM
Or try (assuming values in A1 through G1):
=AVERAGE(IF(A1:G1<>0,A1:G1))
and you must press ctrl-shift-enter after entering the formula

Re: Average 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,

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

Re: Average formula

Posted by Findley Flanagan on November 07, 2001 5:54 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?

Re: Average formula

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

Re: Average formula

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

Re: Average formula

Posted by Aladin Akyurek on November 07, 2001 7:03 AM
Dan & Barrie --

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

Thanks!

Posted by Dan on November 07, 2001 7:53 AM
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,

This archive is from the original message board at www.MrExcel.com.