Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

"Average" Help!!

Posted by George on November 07, 2001 3:14 AM

Hi everyone,

Would someone know how to calculate the average of column but only including in the average cells with a value of 1 or higher, in other words, if the cell has a value of 0 or text it will not be included in the average.

Thanks

Check out our Excel Resources

Re:

Posted by Aladin Akyurek on November 07, 2001 4:02 AM

George,

Let A1:A6 house the data of interest.

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

will give you the kind of average you want.

Aladin

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

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.