Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

Average formula

Posted by Joe P. on June 15, 2001 9:29 AM

I know, based on the questions I've read, that this is probably a very easy question - but im new to Excel. Im trying to come up with a formula to average a column of numbers but not to include any cells in the average that have a value of zero.

Re: Average formula

Posted by Barrie Davidson on June 15, 2001 9:34 AM

The following formula will calculate the average for the cells A1:A5.

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

Barrie

Re: Average formula

Posted by Mark W. on June 15, 2001 9:36 AM

Use {=AVERAGE(IF(A1:A5,A1:A5))} where your values
are contained in the cell range A1:A5. Note:
this is an array formula which must be entered
using the Control+Shift+Enter key combination.
The braces, {}, are not entered by you. They
are supplied by Excel.

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.