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

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

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

Aladin


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,

Aladin

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


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


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,

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,


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.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.