Back to Dates in Excel archive index

Back to archive home

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

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

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

=AVERAGE(IF(A1:G1<>0,A1:G1))

and you must press ctrl-shift-enter after entering the formula

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?

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

Thanks Aladin,

Dan

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

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

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,

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.

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.