How do I average...

[Guest]

How do I average a group of numbers NOT COUNTING the cells that have value
zero or do not have an entry???

Thanks.
Chris

 

[Frank Kabel]

Hi
try the following formula (entered as array formula with
CTRL+SHIFT+ENTER):
=AVERAGE(IF((A1:A100<>"")*(A1:A100<>0),A1:A100))

--
Regards
Frank Kabel
Frankfurt, Germany

"Christopher Anderson" <[email protected]>
schrieb im Newsbeitrag
news:[email protected]...

 

[Chip Pearson]

Chris,

Try the following array formula

=AVERAGE(IF(A1:A10<>0,A1:A10,FALSE))

Since this is an array formula, you must press Ctrl+Shift+Enter
rather than just Enter when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Christopher Anderson"

 

[firefytr]

Well you need not worry about null (blank) cell values as they will be
ignored by the AVERAGE function. Zero's on the otherhand, will give
you skewed results - if they are not values to be Averaged. You have
many options, here is one ...

=SUM(A1:A10)/MAX(1,COUNTIF(A1:A10,"<>0"))


HTH