MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.


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

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.